Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> drop tablespace temp INCLUDING CONTENTS hang

drop tablespace temp INCLUDING CONTENTS hang

From: Guang Mei <>
Date: Tue, 16 Sep 2003 12:49:49 -0800
Message-ID: <>


Oracle 8173 on Solaris 2.8.

I am trying to convert temp tablespace to LMT. My plan is to

1.create another temp tablespace temp123
2.move all users to temp123
3.alter tablespace temp offline
4.drop tablespace temp temp tablespace as LMT
6.move all users to temp
7.drop tablespace temp123.

But I am having problem in Step4. I found that there are still some objects in temp ts even if I put it offline:

SYS_at_remax-SQL> select count(*) from dba_segments where tablespace_name='TEMP';



"drop tablespace temp" resulted in "ORA-01549 tablespace not empty, use INCLUDING CONTENTS option". However when I did

drop tablespace temp INCLUDING CONTENTS;

the sqlplus session just hang and I kept getting the same count(*) from the following query (from another session)

SYS_at_remax-SQL> select count(*) from fet$ where ts# = 3;



I eventually killed the orginal sqlplus session.

I did "ALTER TABLESPACE TEMP coalesce" and it did not seem to help.

So my question is: How can I drop an offlined temp tablespace when there is still segment(s) in it?

I don't know if SMON would clean up the temp ts automatically (Some doc says SMON would not do to TEMP ts). I also read that I could issue

alter session set events 'immediate trace name DROP_SEGMENTS level 3';

to mimic SMON, becuase

SYS_at_remax-SQL> select name, ts# from ts$ where NAME='TEMP';

NAME                                  TS#
------------------------------ ----------
TEMP                                    2

But I am not ready to try it on our production system. Has anyone tries this or has any suggestions? I don't want to bounce db. Would the objects in TEMP ts be cleaned up by SMON if I wait for a couple of days?

TIA. Guang


Please see the official ORACLE-L FAQ:

Author: Guang Mei

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message to: (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Sep 16 2003 - 15:49:49 CDT

Original text of this message