TEMP TABLESPACE IS HUGE AND FULL

[vrudenko]

Hi,

I faced the problem with the Temp tablespace (Oracle 8.1.7, Windows NT4). It's very huge and full.
Please give me some advise to clean it and decrease it's size.

Thank you

 

[lfoata]

Temp tablespace is meant to store temporary tables.

Usually, if it is that huge and still gets full, it means some request must be looping. In that case, you will have to find the guilty object and tell the programmer.
The other reason is that someone is creating permanent tables or indexes in the tablespace.
You can check the objects creation date, and if they are older than a couple of days, drop them or move them.

 

[rcurva]

Adding to lfoata's inputs, after you drop all segments from your temp tablespace, you can resize your datafile to a smaller one.

 

[vrudenko]

Thank you for helping.
But, I beg you to guide me further because I'm not DBA and my company doesn't have DBA. How can I drop segments and resize datafile? What are the steps and statements?

Thank you very much in advance

 

[lfoata]

For all the syntax, I suggest you look into the Oracle SQL Server reference manual. Kind of the bible for Oracle DBAs.