dropping LMT TEMP tablespace taking long time

[samdba]

Hi all,

Oracle 8.1.7.3.0 on Win2000.

My TEMP tablespace is created as LMT, AUTOEXTEND ON.

Its size somehow reached to 30GB.

So, I decided to drop and recreate it.

I have issued the

drop tablespace TEMP including contents;

But it is taking long time to complete the command.

I heared that if it is a LMT, it drops real quick.

It also does not show anything in fet$ for TEMP tablespace.

What should I do?

Thanks in Adv.

[pando]

FYI LMT is locally managed so obviously you cant see anything in data dictionary such as fet$

[ales]

The session that attempted to drop true temporary tablespace with active users waits for uncommited transactions that use the tablespace to finish. Other transactions that wish to use the tablespace will wait to the "drop tablespace" command and then fail.

Originally posted by samdba
What should I do?

You should create another tempfile first:

alter tablespace TEMP add tempfile '...' ...;

Then put the old tempfile offline:

alter database tempfile '...' offline;

This makes all temporary objects (tables, LOBs, sorts) unavailable. Active users may lose their work so it's good to check if there are any (v$sort_usage, v$temporary_lobs) before offlining the tempfile.
New temporary segments are created in the new tempfile.

Then you can try to drop the old tempfile:

alter database tempfile '...' drop;

If there are any uncommited transactions in the TEMP tablespace, "ORA-25152: TEMPFILE cannot be dropped at this time" occurs. Wait and try again. After dropping the tempfile you can remove it from disk.

[samdba]

Hi,

After I disconnected/killed connected sessions, DROP tablespace TEMP was successful.

Thanks for your suggestions.