Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

My PSAPTEMP tablespace always show 100% Full or Used

Dear All ,

Using brtools my tablespace PSAPTEMP always show 100% full or Used, I extend 2 Gb size in PSAPTEMP tablespace but still no solution

Please guide me why it always shows 100% used and what is the performance impact on that

Regards

Shahid

Former Member
replied

Hello Shahid,

> Please guide me why it always shows 100% used and what is the performance impact on that

This is a normal behaviour in case of a temporary tablespace.

If you do sorts, hash-joins, etc. which can not be done in memory-only you allocate extents in the temporary tablespace. If you finish your query, that temporary extents will be marked as free but they still kept allocated. So over the uptime of your database your temporary tablespace will be filled by allocated (but marked as free) extents .. and so you will see it as "full". You can also check the current "real" usage of your temporary tablespace by the view V$TEMPSEG_USAGE:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2164.htm#i1421319

The temporary extents will be deallocated by a database shutdown or by a database start, if the database crashed before.

If you don't get any ORA-01652 by your query .. everything is fine.

Regards

Stefan

1 View this answer in context
Not what you were looking for? View more on this topic or Ask a question