TEMP tablespace is 99% full...
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%) ?
Last edited by Hidayath; 05-27-2003 at 06:44 PM.
Re: TEMP tablespace is 99% full...
Correct. Behaviour changed in 8i, so that extents are allocated and not de-allocated. It saves on resources.
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 ?
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.
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.
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.
check also V$Sort_Usage, u will have better picture.
"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"
Click Here to Expand Forum to Full Width