cancel
Showing results for 
Search instead for 
Did you mean: 

My PSAPTEMP tablespace always show 100% Full or Used

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

stefan_koehler
Active Contributor
0 Kudos

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

Former Member
0 Kudos

Hi Stefan,

I get ORA-01652 unable to extend PSAPTEMP tablespace alert in alertlog file.

I checked the time in which it was generated there is no much load on the system as this is a fresh system and there are no huge background jobs running and dialog user activity is also not high.

Can you help me know the method to find the root cause?

stefan_koehler
Active Contributor
0 Kudos

Hi Balaji,

you can use the view V$SQL_WORKAREA (if the causing SQL is still in the shared pool cache) or you set an additional event "1652 trace name errorstack level 3" and wait for recurrence of this issue.

I personally would go for the latter.

Regards

Stefan

Former Member
0 Kudos

Thanks Stefan. Can you give me some more info on your suggestion. I still am trying to understand your recommendation.

stefan_koehler
Active Contributor
0 Kudos

Hi Balaji,

by default no Oracle trace is created in case of an ORA-01652 error. However with this additional event, the database reacts on event 1652 (= ORA error) and creates and error stack trace file at level 3. Level 3 means "Error stack + function call stack + process state + context area".

So you get the most needed information in a trace and can do the postpone analysis after that issue has reoccurred.

Regards

Stefan

Answers (5)

Answers (5)

Former Member
0 Kudos

Hi Shahid,

what is your operating system it looks like a sparse problem. If it is unix please make a du -k or df -k at the temp datafile and after this also an ls -l at the datafile and show us the output.

Regards

Olaf

Former Member
0 Kudos

After adding new datafile to this tablespace, have you refreshed your DB statistics from DB02?

Regards,

Nick Loy

Former Member
0 Kudos

From DB02, what is the % of utilization of PSAPTEMP?

Regards,

Nick Loy

Former Member
0 Kudos

Thanx Nick for reply, but we are taken daily our offline backup, that's mean our server shutdown daily

Regards

shahid

Former Member
0 Kudos

Try to restart you apps+database....

And let us know the status

Regards,

Nick Loy