Skip to Content
avatar image
Former Member

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

6 Answers

  • Best Answer
    Jan 15, 2010 at 10:35 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • avatar image
    Former Member
    Jan 15, 2010 at 08:00 AM

    Try to restart you apps+database....

    And let us know the status

    Regards,

    Nick Loy

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 15, 2010 at 08:16 AM

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

    Regards

    shahid

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 15, 2010 at 08:47 AM

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

    Regards,

    Nick Loy

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 15, 2010 at 08:49 AM

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

    Regards,

    Nick Loy

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 15, 2010 at 10:05 AM

    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

    Add comment
    10|10000 characters needed characters exceeded