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%) ?
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.