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.
Ales The whole difference between a little boy and an adult man is the price of toys