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
Re: TEMP tablespace is 99% full...
Quote:
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.