Issue with tablespace PSAPTEMP

[former_member759680]

Hello,

Database alert logs (Oracle + Netweaver 7) says 'Unable to extend Tablespace PSAPTEMP'.

But DB02 shows taht PSAPTEMP is totally free.

COuld you please throw some light on this paradox.

Thanks.

[Former Member]

PSAPTEMP is as the name says only used for temporary sort operations. If a sort is too large for the current size, then the operation cancels and the space is more or less instantly freed again.

You have two options:

- increase the space of PSAPTEMP, if your temp space is below 1gb, then do this. If PSAPTEMP is larger, then it depends on the size of your database

- try to find out which operation causes the problem, most likely index creations or ORDER BY with large datasets use a lot of temp space

Regards, Michael

[JPReyes]

PSAPTEMP is used for storage of temporary data so when doing certain tasks data gets moved or created in PSAPTEMP as a intermediate storage to be later released/deleted.

So most likely a task run that required additional space on PSAPTEMP and did not succeed to extend it. Check the errors on the logs and post them here, we might be able to find what cause the issue.

Regards

Juan

[former_member759680]

Our PSAPTEMP is a good 4GB in size.

The alert log reads like this --

Tue Aug 25 08:02:51 2009

Completed checkpoint up to RBA [0xe0b0.2.10], SCN: 1277106986

Tue Aug 25 08:05:08 2009

ORA-1652: unable to extend temp segment by 258 in tablespace PSAPTEMP

ORA-1652: unable to extend temp segment by 258 in tablespace PSAPTEMP

Tue Aug 25 08:06:18 2009

ORA-1652: unable to extend temp segment by 258 in tablespace PSAPTEMP

ORA-1652: unable to extend temp segment by 258 in tablespace PSAPTEMP

Tue Aug 25 08:07:26 2009

ORA-1652: unable to extend temp segment by 258 in tablespace PSAPTEMP

ORA-1652: unable to extend temp segment by 258 in tablespace PSAPTEMP

Tue Aug 25 08:07:27 2009

/* BRARCHIVE */ ALTER DATABASE BACKUP CONTROLFILE TO 'E:\oracle\EBP\SAPReorg\CNTRLEBP.DBF'

Completed: /* BRARCHIVE */ ALTER DATABASE BACKUP CONTROLFILE TO 'E:\oracle\EBP\SAPReorg\CNTRLEBP.DBF'

Tue Aug 25 08:15:13 2009

Incremental checkpoint up to RBA [0xe0b0.108ed.0], current log tail at RBA [0xe0b0.10fed.0]

Tue Aug 25 08:45:14 2009

Incremental checkpoint up to RBA [0xe0b0.18b95.0], current log tail at RBA [0xe0b0.198df.0]

Tue Aug 25 09:15:22 2009

Incremental checkpoint up to RBA [0xe0b0.24c8b.0], current log tail at RBA [0xe0b0.28223.0]

Tue Aug 25 09:16:03 2009

Tue Aug 25 09:21:05 2009

Completed checkpoint up to RBA [0xe0b1.2.10], SCN: 1277162230

Tue Aug 25 09:45:38 2009

Incremental checkpoint up to RBA [0xe0b1.d6f8.0], current log tail at RBA [0xe0b1.1069b.0]

Tue Aug 25 10:15:46 2009

Incremental checkpoint up to RBA [0xe0b1.25f58.0], current log tail at RBA [0xe0b1.27367.0]

Tue Aug 25 10:20:29 2009

Tue Aug 25 10:25:32 2009

Completed checkpoint up to RBA [0xe0b2.2.10], SCN: 1277185489

Tue Aug 25 10:45:54 2009

Incremental checkpoint up to RBA [0xe0b2.89ce.0], current log tail at RBA [0xe0b2.c956.0]

Tue Aug 25 11:15:55 2009

Incremental checkpoint up to RBA [0xe0b2.221e3.0], current log tail at RBA [0xe0b2.25707.0]

Tue Aug 25 11:26:12 2009

Tue Aug 25 11:31:14 2009

Completed checkpoint up to RBA [0xe0b3.2.10], SCN: 1277208797

Tue Aug 25 11:45:56 2009

Incremental checkpoint up to RBA [0xe0b3.aabc.0], current log tail at RBA [0xe0b3.d507.0]

Tue Aug 25 12:05:42 2009

ORA-1652: unable to extend temp segment by 258 in tablespace PSAPTEMP

ORA-1652: unable to extend temp segment by 258 in tablespace PSAPTEMP

Tue Aug 25 12:07:05 2009

ORA-1652: unable to extend temp segment by 258 in tablespace PSAPTEMP

ORA-1652: unable to extend temp segment by 258 in tablespace PSAPTEMP

ORA-1652: unable to extend temp segment by 258 in tablespace PSAPTEMP

Tue Aug 25 12:07:07 2009

/* BRARCHIVE */ ALTER DATABASE BACKUP CONTROLFILE TO 'E:\oracle\EBP\SAPReorg\CNTRLEBP.DBF'

Completed: /* BRARCHIVE */ ALTER DATABASE BACKUP CONTROLFILE TO 'E:\oracle\EBP\SAPReorg\CNTRLEBP.DBF'

Tue Aug 25 12:15:56 2009

Incremental checkpoint up to RBA [0xe0b3.23fd4.0], current log tail at RBA [0xe0b3.243ef.0]

Tue Aug 25 12:28:00 2009

Tue Aug 25 12:33:07 2009

Completed checkpoint up to RBA [0xe0b4.2.10], SCN: 1277250318

Tue Aug 25 12:46:13 2009

Incremental checkpoint up to RBA [0xe0b4.31fa.0], current log tail at RBA [0xe0b4.884c.0]

Tue Aug 25 13:16:29 2009

Incremental checkpoint up to RBA [0xe0b4.17879.0], current log tail at RBA [0xe0b4.1b768.0]

Tue Aug 25 13:46:29 2009

Incremental checkpoint up to RBA [0xe0b4.2657a.0], current log tail at RBA [0xe0b4.2a3d8.0]

Tue Aug 25 13:50:17 2009

Tue Aug 25 13:55:18 2009

Completed checkpoint up to RBA [0xe0b5.2.10], SCN: 1277273370

Tue Aug 25 14:16:30 2009

Incremental checkpoint up to RBA [0xe0b5.11df5.0], current log tail at RBA [0xe0b5.13fa3.0]

Tue Aug 25 14:46:31 2009

Incremental checkpoint up to RBA [0xe0b5.24569.0], current log tail at RBA [0xe0b5.25e9a.0]

Tue Aug 25 14:54:21 2009

Tue Aug 25 14:59:14 2009

Completed checkpoint up to RBA [0xe0b6.2.10], SCN: 1277297035

Tue Aug 25 15:16:32 2009

Incremental checkpoint up to RBA [0xe0b6.194e5.0], current log tail at RBA [0xe0b6.1a2cc.0]

Tue Aug 25 15:42:08 2009

Tue Aug 25 15:46:32 2009

Incremental checkpoint up to RBA [0xe0b6.294ce.0], current log tail at RBA [0xe0b7.8dfd.0]

Tue Aug 25 15:47:10 2009

Completed checkpoint up to RBA [0xe0b7.2.10], SCN: 1277319066

Edited by: Gautam Poddar on Aug 26, 2009 1:07 PM

[JPReyes]

Well... can you check what was running around 8AM that could have required extra space in PSAPTEMP?... specially using BRTOOLS

Regards

Juan

[Former Member]

Gautam,

ORA-01652: This error is caused by a tablespace overflow. As a rule of thumb, PSAPTEMP should be at least twice as
 large as the largest index. If an overflow occurs despite this size, this is frequently due to external reasons such as incorrect 
Oracle parameter settings, large-scale parallel processing or unfavorable access paths. See also Note 3155.

Check following note for detailed info.

[https://websmp130.sap-ag.de/sap(bD1lbiZjPTAwMQ==)/bc/bsp/spn/sapnotes/index2.htm?numm=659946]

Hope this helps.

Manoj

[Former Member]

Hi Gautam Poddar,

This may have happen in case of database change that the temp tablespace is full but after the transaction is complete the table space will be free.

Regards

Ashok Dalai