TEMP tablespace is 99% full...

[Hidayath]

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

[slimdave]

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.

[Hidayath]

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

[slimdave]

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.

[abhaysk]

check also V$Sort_Usage, u will have better picture.