on 01-15-2010 5:29 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
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
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
After adding new datafile to this tablespace, have you refreshed your DB statistics from DB02?
Regards,
Nick Loy
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
From DB02, what is the % of utilization of PSAPTEMP?
Regards,
Nick Loy
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanx Nick for reply, but we are taken daily our offline backup, that's mean our server shutdown daily
Regards
shahid
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Try to restart you apps+database....
And let us know the status
Regards,
Nick Loy
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
24 | |
11 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.