TEMP tablespace is 99% full...
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: TEMP tablespace is 99% full...

Hybrid View

  1. #1
    Join Date
    Nov 2000
    Location
    San Diego
    Posts
    24

    TEMP tablespace is 99% full...

    Hi,

    We are on W2K with 8i.

    We have a TEMP tablespace defined as 'Temporary'. Size 5 GB.

    Today I see it is about 99% used. I belive some one ran a huge query. Now my question after the temp tablespace is used by the oracle for sorting, and the work is completed, does it not return the space back ? If not, how do we get back the unused space (inactive space).. I don't know what word should I use here for this space. Or How do I free the space ?

    If someone wants to run a similar query/report will they get an error just becaue it is already 99% used or this new query would make use of the already filled but inactive tablespace(99%) ?

    Thanks,
    -Hidayath
    Last edited by Hidayath; 05-27-2003 at 06:44 PM.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253

    Re: TEMP tablespace is 99% full...

    Originally posted by Hidayath
    Now my question after the temp tablespace is used by the oracle for sorting, and the work is completed, does it not return the space back ?
    Correct. Behaviour changed in 8i, so that extents are allocated and not de-allocated. It saves on resources.

    Use the sys view V$TEMPSEG_USAGE to monitor the usage of the temp tablespace.

    also, read the 8i documentation concerning use of temporary tablespaces.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Nov 2000
    Location
    San Diego
    Posts
    24

    Thanks ...

    Thanks slimdave,

    After posting this I went to metalink and got some answers to this.

    I am getting error (table or view doesnot exist) when i quried V$TEMPSEG_USAGE view. I used sys.V$TEMPSEG_USAGE still I get the same error. Do you mean v$sort_segment view ?

    When I queried the above view I find over 70% of the Blocks are free.

    This tells me that though the TEMP space is full by over 99% there are over 70% of free blocks.

    Thanks for you reply.

    -Hidayath

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I was looking at 9iR2 documentation for that view definition -- maybe it's not in 8i.

    check the 8i Reference for a definition of the view, but it sounds correct.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    check also V$Sort_Usage, u will have better picture.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width