Hi! Check from v$session_wait, on what is your dropping session waiting.
Tanel. ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, September 16, 2003 11:49 PM > Hi: > > 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 > 5.re-create 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: > > [EMAIL PROTECTED]> select count(*) from dba_segments where > tablespace_name='TEMP'; > > COUNT(*) > ---------- > 1 > > > "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) > > [EMAIL PROTECTED]> select count(*) from fet$ where ts# = 3; > > COUNT(*) > ---------- > 214 > > > 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 > > [EMAIL PROTECTED]> 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: http://www.orafaq.net > -- > Author: Guang Mei > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
