Re: drop tablespace temp INCLUDING CONTENTS hang
Hi, I do not know what do you means by saying hangs.Drop a non-temporary temp tablespace does take much time if it was ever used heavily. I remember when I came to current company they do not used temporary tablespace on 8172 db. Drop the old temporary tablespace took about 30 minutes.But I waited and it did finish:) Allocate extents and deallocate extents via dictionay is protected by a space management enqueue, if this takes rather long time, there maybe some session want to allocate extents fail. Regards zhu chao msn:[EMAIL PROTECTED] www.cnoug.org - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, September 18, 2003 2:54 AM > Yes, you are right, I should have looked at ts# = 2. BTW, I tried several > ways to resolve this issue by doing these and none of them worked, > > 1. alter tablespace temp default storage(pctincrease 0); -- a tip from > Steve Adam's site > > 2. alter session set events 'immediate trace name DROP_SEGMENTS level 3'; > > 3. oradebug wakeup 6; -- 6 is sid for SMON process > > 4. Create a small table with a primary key constraint then drop the > constraint. Iwas hoping that shadow process will change the associated index > to a temporary segment and post smon to clear it up. > > 5. create a large table that will fail: > CREATE TABLE junk ( c1 varchar2(1) ) tablespace DATA storage ( initial 2000M > next 2000M) ; > > Finally I had to bounce the instance, that cleared up the segment in temp > tablespace, so that it could be dropped. > > Guang > > -Original Message- > zhu chao > Sent: Tuesday, September 16, 2003 10:25 PM > To: Multiple recipients of list ORACLE-L > > > Hi, guang: > First, I think step 5,6,7 are not necessary. Why bother change back to > that named tablespace? To user, temporary tablespace name is totally > transparent to them and to the application. > You should use drop tablespace temp including contents, but the query > you used maybe is wrong. your temp tablespace is TS#=2, but your query is > :select count(*) from fet$ where ts# = 3; (you are using 3 here). > > Regards > zhu chao > msn:[EMAIL PROTECTED] > www.cnoug.org > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Wednesday, September 17, 2003 4:49 AM > > > > 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# > > -- -- > > TEMP2 > > > > > > 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 t
RE: drop tablespace temp INCLUDING CONTENTS hang
Yes, you are right, I should have looked at ts# = 2. BTW, I tried several ways to resolve this issue by doing these and none of them worked, 1. alter tablespace temp default storage(pctincrease 0); -- a tip from Steve Adam's site 2. alter session set events 'immediate trace name DROP_SEGMENTS level 3'; 3. oradebug wakeup 6; -- 6 is sid for SMON process 4. Create a small table with a primary key constraint then drop the constraint. Iwas hoping that shadow process will change the associated index to a temporary segment and post smon to clear it up. 5. create a large table that will fail: CREATE TABLE junk ( c1 varchar2(1) ) tablespace DATA storage ( initial 2000M next 2000M) ; Finally I had to bounce the instance, that cleared up the segment in temp tablespace, so that it could be dropped. Guang -Original Message- zhu chao Sent: Tuesday, September 16, 2003 10:25 PM To: Multiple recipients of list ORACLE-L Hi, guang: First, I think step 5,6,7 are not necessary. Why bother change back to that named tablespace? To user, temporary tablespace name is totally transparent to them and to the application. You should use drop tablespace temp including contents, but the query you used maybe is wrong. your temp tablespace is TS#=2, but your query is :select count(*) from fet$ where ts# = 3; (you are using 3 here). Regards zhu chao msn:[EMAIL PROTECTED] www.cnoug.org - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, September 17, 2003 4:49 AM > 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# > -- -- > TEMP2 > > > 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: zhu chao 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: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mail
Re: drop tablespace temp INCLUDING CONTENTS hang
Hi, guang: First, I think step 5,6,7 are not necessary. Why bother change back to that named tablespace? To user, temporary tablespace name is totally transparent to them and to the application. You should use drop tablespace temp including contents, but the query you used maybe is wrong. your temp tablespace is TS#=2, but your query is :select count(*) from fet$ where ts# = 3; (you are using 3 here). Regards zhu chao msn:[EMAIL PROTECTED] www.cnoug.org - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, September 17, 2003 4:49 AM > 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# > -- -- > TEMP2 > > > 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: zhu chao 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).
Re: drop tablespace temp INCLUDING CONTENTS hang
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# > -- -- > TEMP2 > > > 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).