ORA-1652: Unable to extend temp segment by 128 in tablespace PSAPTEMP

[Former Member]

Hi,

I have upgraded to oracle version 11.2.0.4.0 recently. After upgrade I receive "ORA-1652: Unable to extend temp segment by 128 in tablespace PSAPTEMP" everyday in database check log. There is sufficient space for PSAPTEMP and the status of tablespace in RZ20 is green. I have checked with SQL command "select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks from gv$sort_segment" which returns that there are free blocks available upto 3 Gb.

When I checked tablespace status with BRTOOLS, it shows used space is 0%.

Could anyone tell me how to correct this alert message???

Thanks and regards,

Ganesan

[janos_mucsi-besze]

Hi Ganesan.

When did you query PSAPTEMP? Was it checked while that partical process/SAP Job was running?
PSAPTEMP is used to store data while sorting,grouping,etc. if PGA is not sufficient. It is quite normal to get ORA-01652 while a long running operation is performing a huge sort operation.

As a rule, PSAPTEMP should be 2-2,5 time bigger then the biggest index in the database.

Regards,

János

[Former Member]

Hello Janos Mucsi_Besze,

Thanks for your reply. PSAPTEMP size is 3 times bigger than the biggest index in the database. And I can see the presence of tempfiles in OS/ SAP & DB level. I set auto extension ON for this tablespace and let it to extend upto 20 G as suggested by Deepak Kori. But still the problem exists.

Can anyone please explain me what is 0:0:0:0:0 entry and its effects, under heading "Largest [KB]" in BRtool screenshot above? Because I only see this entry in systems where I face problem like this.

Thanks for your help.

Regards,

Ganesan

[Former Member]

Hello Ganesan,

let's check and identify your tablespace datafiles first with that SQL command:

via sqlplus:

select NAME,bytes from v$tempfile;

Do you see a result like this?

/oracle/SID/sapdata1/temp_1/temp.data1

/oracle/SID/sapdata2/temp_2/temp.data2

Regards,

Serhat

[former_member207186]

Hi Ganesan,

ORA-01652 is caused by a tablespace overflow. For possible reasons and solutions, please refer to SAP notes below:
659946 - FAQ: Temporary tablespaces, point 7 
825653 - Oracle: Common misconceptions, point 45
   3155 - Termination due to tablespace overflow

Regards,
Bíborka

[Former Member]

Hi Biborka,

Thanks for your reply. Most of the forum and notes related to ORA-1652 say that it is related to tablespace overflow. But in my case, I could clearly see in SAP and well as in DB that there are free space available and used space 0% for PSAPTEMP. If you could observe in the screenshot, it shows "0:0:0:0:0" under heading "Largest [KB]" which I couldn't  understand, but I don't see any entries with all 0 (Zeros) for any other tablespaces. It also shows the presence of "tempfile" too. But still the problem exists. Thanks for the notes reference.

Regards,

Ganesan

[former_member188883]

Hi Ganesan,

From my understanding I see that some process in your SAP system which needs more temp space.

Also your Temp tablesapce does not have auto extend ON.

To avoid this error you may add a new datafile of 1 GB and make it auto extend upto 20 GB.

Post this observe the system and share the results.

Hope this helps.

Regards,

Deepak Kori