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-- Mailing list and web hosting services
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 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
drop tablespace temp INCLUDING CONTENTS hang
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).
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).
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
OMF ? What was the name of the file ? You can tell from that whether its omf or not. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, July 02, 2003 02:05 : Hi List, : : Today I have dropped a big tablespace(4 Gig) as soon as I dropped it the : data file also disappeared from the list of datafiles, as I know the : datafile must be removed manually Any idea? : : I am using 9.0.1 on solaris 2.8 : : Thanks, : : Hamid Alavi : : Office : 818-737-0526 : Cell phone : 818-416-5095 : : -- : Please see the official ORACLE-L FAQ: http://www.orafaq.net : -- : Author: Hamid Alavi : 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: [EMAIL PROTECTED] 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).
Drop Tablespace
Hi List, Today I have dropped a big tablespace(4 Gig) as soon as I dropped it the data file also disappeared from the list of datafiles, as I know the datafile must be removed manually Any idea? I am using 9.0.1 on solaris 2.8 Thanks, Hamid Alavi Office : 818-737-0526 Cell phone : 818-416-5095 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hamid Alavi 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
It dissappered from the data dict... but it should exist at os level... you need to rm it... check it... HTH JL --- Hamid Alavi [EMAIL PROTECTED] wrote: Hi List, Today I have dropped a big tablespace(4 Gig) as soon as I dropped it the data file also disappeared from the list of datafiles, as I know the datafile must be removed manually Any idea? I am using 9.0.1 on solaris 2.8 Thanks, Hamid Alavi Office : 818-737-0526 Cell phone : 818-416-5095 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hamid Alavi 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). __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado 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
Normal behaviour. If you say 'drop' to Oracle it knows it no longer has to care about it, and so does it. From then, the file has no more special for Oracle than say your background wallpaper image. Hamid Alavi wrote: Hi List, Today I have dropped a big tablespace(4 Gig) as soon as I dropped it the data file also disappeared from the list of datafiles, as I know the datafile must be removed manually Any idea? I am using 9.0.1 on solaris 2.8 Thanks, Hamid Alavi Office : 818-737-0526 Cell phone : 818-416-5095 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hamid Alavi 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). -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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
There's a new feature in 9i that can remove the os file also Ramon E. Estevez [EMAIL PROTECTED] 809-535-8994 -Original Message- Jose Luis Delgado Sent: Tuesday, July 01, 2003 5:13 PM To: Multiple recipients of list ORACLE-L It dissappered from the data dict... but it should exist at os level... you need to rm it... check it... HTH JL --- Hamid Alavi [EMAIL PROTECTED] wrote: Hi List, Today I have dropped a big tablespace(4 Gig) as soon as I dropped it the data file also disappeared from the list of datafiles, as I know the datafile must be removed manually Any idea? I am using 9.0.1 on solaris 2.8 Thanks, Hamid Alavi Office : 818-737-0526 Cell phone : 818-416-5095 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hamid Alavi 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). __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado 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: Ramon E. Estevez 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
Hamid Is there any chance this was created as an Oracle Managed File (OMF)? http://otn.oracle.com/products/oracle9i/daily/04.html They have special file names. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, July 01, 2003 3:35 PM To: Multiple recipients of list ORACLE-L Hi List, Today I have dropped a big tablespace(4 Gig) as soon as I dropped it the data file also disappeared from the list of datafiles, as I know the datafile must be removed manually Any idea? I am using 9.0.1 on solaris 2.8 Thanks, Hamid Alavi Office : 818-737-0526 Cell phone : 818-416-5095 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hamid Alavi 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: DENNIS WILLIAMS 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
Yep, if you type drop tablespace name including contents and datafiles; the datafiles are removed. Also, if you are using OMF (oracle managed files) by some odd reason then dropping tablespace also deletes the datafiles, even if you don't specify including contents and datafiles option. A small example below.. Tanel. SQL alter session set db_create_file_dest=G:\; Session altered. SQL create tablespace g; Tablespace created. SQL select name from v$datafile; NAME C:\ORACLE\ORA92\ORCL\SYSTEM01.DBF C:\ORACLE\ORA92\ORCL\UNDOTBS01.DBF C:\ORACLE\ORA92\ORCL\CWMLITE01.DBF C:\ORACLE\ORA92\ORCL\DRSYS01.DBF C:\ORACLE\ORA92\ORCL\EXAMPLE01.DBF C:\ORACLE\ORA92\ORCL\INDX01.DBF C:\ORACLE\ORA92\ORCL\ODM01.DBF C:\ORACLE\ORA92\ORCL\TOOLS01.DBF C:\ORACLE\ORA92\ORCL\USERS01.DBF C:\ORACLE\ORA92\ORCL\XDB01.DBF C:\ORACLE\ORA92\ORCL\TS2K01.DBF NAME G:\O1_MF_G_ZJ3XXJ00_.DBF 12 rows selected. SQL ho dir G:\O1_MF_G_ZJ3XXJ00_.DBF Volume in drive G has no label. Volume Serial Number is 7042-6228 Directory of G:\ 02.07.2003 00:05 104 865 792 O1_MF_G_ZJ3XXJ00_.DBF 1 File(s)104 865 792 bytes 0 Dir(s) 298 082 304 bytes free SQL drop tablespace g; Tablespace dropped. SQL ho dir G:\O1_MF_G_ZJ3XXJ00_.DBF Volume in drive G has no label. Volume Serial Number is 7042-6228 Directory of G:\ File Not Found SQL - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, July 02, 2003 12:28 AM There's a new feature in 9i that can remove the os file also Ramon E. Estevez [EMAIL PROTECTED] 809-535-8994 -Original Message- Jose Luis Delgado Sent: Tuesday, July 01, 2003 5:13 PM To: Multiple recipients of list ORACLE-L It dissappered from the data dict... but it should exist at os level... you need to rm it... check it... HTH JL --- Hamid Alavi [EMAIL PROTECTED] wrote: Hi List, Today I have dropped a big tablespace(4 Gig) as soon as I dropped it the data file also disappeared from the list of datafiles, as I know the datafile must be removed manually Any idea? I am using 9.0.1 on solaris 2.8 Thanks, Hamid Alavi Office : 818-737-0526 Cell phone : 818-416-5095 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hamid Alavi 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). __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado 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: Ramon E. Estevez 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
Re: Drop Tablespace
datafiles gone from v$datafile or from OS ? If it's from your controlfile then this is okay . But u will have to remove os files manually . -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 01, 2003 1:35 PM Hi List, Today I have dropped a big tablespace(4 Gig) as soon as I dropped it the data file also disappeared from the list of datafiles, as I know the datafile must be removed manually Any idea? I am using 9.0.1 on solaris 2.8 Thanks, Hamid Alavi Office : 818-737-0526 Cell phone : 818-416-5095 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hamid Alavi 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: AK 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
It's about time, don't you think?!! Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Tuesday, July 01, 2003 6:05 PM To: Multiple recipients of list ORACLE-L Yep, if you type drop tablespace name including contents and datafiles; the datafiles are removed. Also, if you are using OMF (oracle managed files) by some odd reason then dropping tablespace also deletes the datafiles, even if you don't specify including contents and datafiles option. A small example below.. Tanel. SQL alter session set db_create_file_dest=G:\; Session altered. SQL create tablespace g; Tablespace created. SQL select name from v$datafile; NAME C:\ORACLE\ORA92\ORCL\SYSTEM01.DBF C:\ORACLE\ORA92\ORCL\UNDOTBS01.DBF C:\ORACLE\ORA92\ORCL\CWMLITE01.DBF C:\ORACLE\ORA92\ORCL\DRSYS01.DBF C:\ORACLE\ORA92\ORCL\EXAMPLE01.DBF C:\ORACLE\ORA92\ORCL\INDX01.DBF C:\ORACLE\ORA92\ORCL\ODM01.DBF C:\ORACLE\ORA92\ORCL\TOOLS01.DBF C:\ORACLE\ORA92\ORCL\USERS01.DBF C:\ORACLE\ORA92\ORCL\XDB01.DBF C:\ORACLE\ORA92\ORCL\TS2K01.DBF NAME G:\O1_MF_G_ZJ3XXJ00_.DBF 12 rows selected. SQL ho dir G:\O1_MF_G_ZJ3XXJ00_.DBF Volume in drive G has no label. Volume Serial Number is 7042-6228 Directory of G:\ 02.07.2003 00:05 104 865 792 O1_MF_G_ZJ3XXJ00_.DBF 1 File(s)104 865 792 bytes 0 Dir(s) 298 082 304 bytes free SQL drop tablespace g; Tablespace dropped. SQL ho dir G:\O1_MF_G_ZJ3XXJ00_.DBF Volume in drive G has no label. Volume Serial Number is 7042-6228 Directory of G:\ File Not Found SQL - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, July 02, 2003 12:28 AM There's a new feature in 9i that can remove the os file also Ramon E. Estevez [EMAIL PROTECTED] 809-535-8994 -Original Message- Jose Luis Delgado Sent: Tuesday, July 01, 2003 5:13 PM To: Multiple recipients of list ORACLE-L It dissappered from the data dict... but it should exist at os level... you need to rm it... check it... HTH JL --- Hamid Alavi [EMAIL PROTECTED] wrote: Hi List, Today I have dropped a big tablespace(4 Gig) as soon as I dropped it the data file also disappeared from the list of datafiles, as I know the datafile must be removed manually Any idea? I am using 9.0.1 on solaris 2.8 Thanks, Hamid Alavi Office : 818-737-0526 Cell phone : 818-416-5095 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hamid Alavi 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). __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado 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: Ramon E. Estevez 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
Re: Drop Tablespace
Hi! I personally still prefer to do dropping manually. First drop objects from tablespace, then drop tablespace, then check with fuser whether anyone still uses the datafile, then rm datafile. It takes longer, but I like to have more control over things (including contents option can be quite dangerous for example..) Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, July 02, 2003 1:16 AM It's about time, don't you think?!! Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Tuesday, July 01, 2003 6:05 PM To: Multiple recipients of list ORACLE-L Yep, if you type drop tablespace name including contents and datafiles; the datafiles are removed. Also, if you are using OMF (oracle managed files) by some odd reason then dropping tablespace also deletes the datafiles, even if you don't specify including contents and datafiles option. A small example below.. Tanel. SQL alter session set db_create_file_dest=G:\; Session altered. SQL create tablespace g; Tablespace created. SQL select name from v$datafile; NAME -- -- C:\ORACLE\ORA92\ORCL\SYSTEM01.DBF C:\ORACLE\ORA92\ORCL\UNDOTBS01.DBF C:\ORACLE\ORA92\ORCL\CWMLITE01.DBF C:\ORACLE\ORA92\ORCL\DRSYS01.DBF C:\ORACLE\ORA92\ORCL\EXAMPLE01.DBF C:\ORACLE\ORA92\ORCL\INDX01.DBF C:\ORACLE\ORA92\ORCL\ODM01.DBF C:\ORACLE\ORA92\ORCL\TOOLS01.DBF C:\ORACLE\ORA92\ORCL\USERS01.DBF C:\ORACLE\ORA92\ORCL\XDB01.DBF C:\ORACLE\ORA92\ORCL\TS2K01.DBF NAME -- -- G:\O1_MF_G_ZJ3XXJ00_.DBF 12 rows selected. SQL ho dir G:\O1_MF_G_ZJ3XXJ00_.DBF Volume in drive G has no label. Volume Serial Number is 7042-6228 Directory of G:\ 02.07.2003 00:05 104 865 792 O1_MF_G_ZJ3XXJ00_.DBF 1 File(s)104 865 792 bytes 0 Dir(s) 298 082 304 bytes free SQL drop tablespace g; Tablespace dropped. SQL ho dir G:\O1_MF_G_ZJ3XXJ00_.DBF Volume in drive G has no label. Volume Serial Number is 7042-6228 Directory of G:\ File Not Found SQL - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, July 02, 2003 12:28 AM There's a new feature in 9i that can remove the os file also Ramon E. Estevez [EMAIL PROTECTED] 809-535-8994 -Original Message- Jose Luis Delgado Sent: Tuesday, July 01, 2003 5:13 PM To: Multiple recipients of list ORACLE-L It dissappered from the data dict... but it should exist at os level... you need to rm it... check it... HTH JL --- Hamid Alavi [EMAIL PROTECTED] wrote: Hi List, Today I have dropped a big tablespace(4 Gig) as soon as I dropped it the data file also disappeared from the list of datafiles, as I know the datafile must be removed manually Any idea? I am using 9.0.1 on solaris 2.8 Thanks, Hamid Alavi Office : 818-737-0526 Cell phone : 818-416-5095 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hamid Alavi 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). __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado 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: Ramon E. Estevez INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: drop tablespace including contents
I fully agree. Unfortunately management insists on it. Russ -Original Message- Sent: Tuesday, August 20, 2002 9:13 PM To: Multiple recipients of list ORACLE-L Dick, There is absolutely *nothing* that SAPDBA does that a reasonably knowledgeable DBA can't do from his of her favorite toolset. ( vi, Perl and sqlplus for me :) SAP types have it drummed into their heads that the only proper way to do anything DBA work is via SAPDBA. I refuse to use it, and it just drives the SAP consultants crazy. There are many cases where a good DBA can do a much better job than SAPDBA. The tablespace reorganization is a good example. Trying to 'drop tablespace including contents' with 3500 tables is not a terribly bright way of going about it. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 08/20/2002 02:43 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re:drop tablespace including contents Russ, Your high usage of RBS was due to the updates being done to the system data dictionary. Since you were dropping a tablespace and contents the DDL statements for the individual objects (tables and indexes) needs to be done first, but I've a funny idea from practice that Oracle does not do an implicit commit in this case but instead holds on till the end. This makes dropping a tablespace with the including contents caviot very nasty. Thank GOD we never implemented SAP over here. I've heard nothing but bad about SAP and sapdba. Dick Goulet Reply Separator Author: Brooks; Russ [EMAIL PROTECTED] Date: 8/20/2002 11:13 AM Hi, This past weekend we experienced a problem on a production database, and I would like to try to determine what went wrong, how to avoid it in the future, and any better ways of dealing with it should it be encountered again. After moving some large objects out of tablespace to spread I/O, we wanted to reorganize the old tablespace to remove some fragmentation. The tool we were using, sapdba, does not readily permit you to drop the individual tables between the export and the drop tablespace including contents. Since the tablespace had over 3500 tables the drop tablespace was expected to take a long time. We also defined a large rollback segment for use this weekend, although with only maxextents of 100. When Oracle tried to allocate the 101 extent in the RBS, error messages were issued and things came to a grinding halt. sar indicated disk I/O to the new RBS, but not to any of the datafiles. We waited several hours, but the situation did not appear to change. Shutdown immediate did not work. We could alter the datafiles back online, but not the tablespace. Since it was production, the decision was made to restore to a recent backup. 1. Was the rollback activity due solely to storing and restoring DDL for the tables and indices? 2. Once the RBS was unable to extend, was the drop tablespace including contents dead? We tried to alter maxextents on the RBS, but did not get a response from the system. Was that the appropriate reaction to this problem. 3. A join of v$session and v$sql did not indicate any active SQL. How should we have monitored the progress of what we assume was rollback activity? Any way to estimate how much or how long the rollback would take? 4. If the database were shutdown during the rollback I assume the rollback would recommence when Oracle came back up. Would it start where it left off or start from scratch. It was my impression that it is marking the header blocks as it goes, but I would like to check. Thanks, Russ Brooks !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 4.0 Transitional//EN HTMLHEAD META HTTP-EQUIV=Content-Type CONTENT=text/html; charset=iso-8859-1 META content=MSHTML 5.50.4807.2300 name=GENERATOR/HEAD BODY DIVHi, BRThis past weekend we experienced a problem on a production database, and I would like to try to determine what went wrong, how to avoid it in the future, and any better ways of dealing with it should it be encountered again. BRAfter moving some large objects out of tablespace to spread I/O, we wanted to reorganize the old tablespace to remove some fragmentation. The tool we were using, sapdba, does not readily permit you to drop the individual tables between the export and the drop tablespace including contents. Since the tablespace had over 3500 tables the drop tablespace was expected to take a long time. We also defined a large rollback segment for use this weekend, although with only maxextents of 100. When Oracle tried to allocate the 101 extent in the RBS, error messages were issued and things came to a grinding halt. sar indicated disk I/O to the new RBS, but not to any of the datafiles. We waited several hours, but the situation did not appear to change. BRShutdown immediate did not work. We could alter the datafiles back
RE: drop tablespace including contents
Russ, You have my sympathies. I've been managing SAP databases for the last 7 years, and the last time I let SAPDBA control a reorg was 6.95 years ago. It performs some tasks reasonably well, like datafile additions and managing CBO statistics refreshes (especially since SAP wants certain tables to be left without statistics). But for reorgs, I prefer more direct control. Perhaps you can use this SNAFU to convince management to have sapdba generate the scripts, then allow you to modified them appropriately. Mike Hand Polaroid Corp. -Original Message- Sent: Wednesday, August 21, 2002 9:03 AM To: Multiple recipients of list ORACLE-L I fully agree. Unfortunately management insists on it. Russ -Original Message- Sent: Tuesday, August 20, 2002 9:13 PM To: Multiple recipients of list ORACLE-L Dick, There is absolutely *nothing* that SAPDBA does that a reasonably knowledgeable DBA can't do from his of her favorite toolset. ( vi, Perl and sqlplus for me :) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hand, Michael T INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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 including contents
Russ - A couple of ideas for you. 1. Check directly in Oracle before you perform a task with the tool. And check afterward to verify what the tool did. This will make you a better DBA. And in a future job interview, you'll have a more interesting conversation than just saying duh the only way I know to work with Oracle is to use this tool. 2. Make a list of incidents where the tool messed things up or didn't use the best method. Management is often impressed with detailed, documented facts. It can also open a dialogue with the SAP tool developers. Often those people want good feedback since they are developers, not production DBAs (years ago I held that job at a different ERP vendor). 3. Keep in mind that you face the same issues in using any tool, even if it is Oracle's OEM, which management at some sites mandate their DBAs use. Sometimes more efficient, but you are one degree removed from Oracle. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, August 21, 2002 8:03 AM To: Multiple recipients of list ORACLE-L I fully agree. Unfortunately management insists on it. Russ -Original Message- Sent: Tuesday, August 20, 2002 9:13 PM To: Multiple recipients of list ORACLE-L Dick, There is absolutely *nothing* that SAPDBA does that a reasonably knowledgeable DBA can't do from his of her favorite toolset. ( vi, Perl and sqlplus for me :) SAP types have it drummed into their heads that the only proper way to do anything DBA work is via SAPDBA. I refuse to use it, and it just drives the SAP consultants crazy. There are many cases where a good DBA can do a much better job than SAPDBA. The tablespace reorganization is a good example. Trying to 'drop tablespace including contents' with 3500 tables is not a terribly bright way of going about it. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 08/20/2002 02:43 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re:drop tablespace including contents Russ, Your high usage of RBS was due to the updates being done to the system data dictionary. Since you were dropping a tablespace and contents the DDL statements for the individual objects (tables and indexes) needs to be done first, but I've a funny idea from practice that Oracle does not do an implicit commit in this case but instead holds on till the end. This makes dropping a tablespace with the including contents caviot very nasty. Thank GOD we never implemented SAP over here. I've heard nothing but bad about SAP and sapdba. Dick Goulet Reply Separator Author: Brooks; Russ [EMAIL PROTECTED] Date: 8/20/2002 11:13 AM Hi, This past weekend we experienced a problem on a production database, and I would like to try to determine what went wrong, how to avoid it in the future, and any better ways of dealing with it should it be encountered again. After moving some large objects out of tablespace to spread I/O, we wanted to reorganize the old tablespace to remove some fragmentation. The tool we were using, sapdba, does not readily permit you to drop the individual tables between the export and the drop tablespace including contents. Since the tablespace had over 3500 tables the drop tablespace was expected to take a long time. We also defined a large rollback segment for use this weekend, although with only maxextents of 100. When Oracle tried to allocate the 101 extent in the RBS, error messages were issued and things came to a grinding halt. sar indicated disk I/O to the new RBS, but not to any of the datafiles. We waited several hours, but the situation did not appear to change. Shutdown immediate did not work. We could alter the datafiles back online, but not the tablespace. Since it was production, the decision was made to restore to a recent backup. 1. Was the rollback activity due solely to storing and restoring DDL for the tables and indices? 2. Once the RBS was unable to extend, was the drop tablespace including contents dead? We tried to alter maxextents on the RBS, but did not get a response from the system. Was that the appropriate reaction to this problem. 3. A join of v$session and v$sql did not indicate any active SQL. How should we have monitored the progress of what we assume was rollback activity? Any way to estimate how much or how long the rollback would take? 4. If the database were shutdown during the rollback I assume the rollback would recommence when Oracle came back up. Would it start where it left off or start from scratch. It was my impression that it is marking the header blocks as it goes, but I would like to check. Thanks, Russ Brooks !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 4.0 Transitional//EN HTMLHEAD META HTTP-EQUIV=Content-Type CONTENT=text/html; charset=iso-8859-1 META content=MSHTML 5.50.4807.2300 name=GENERATOR
RE: drop tablespace including contents
Thanks Dennis, Yeah, I know how to do the work without the tool, using sqlplus. That's not the problem. Management won't even allow us to use OEM for maintenance tasks, only sapdba. My hands are tied, at least for the moment. Russ -Original Message- Sent: Wednesday, August 21, 2002 11:39 AM To: Multiple recipients of list ORACLE-L Russ - A couple of ideas for you. 1. Check directly in Oracle before you perform a task with the tool. And check afterward to verify what the tool did. This will make you a better DBA. And in a future job interview, you'll have a more interesting conversation than just saying duh the only way I know to work with Oracle is to use this tool. 2. Make a list of incidents where the tool messed things up or didn't use the best method. Management is often impressed with detailed, documented facts. It can also open a dialogue with the SAP tool developers. Often those people want good feedback since they are developers, not production DBAs (years ago I held that job at a different ERP vendor). 3. Keep in mind that you face the same issues in using any tool, even if it is Oracle's OEM, which management at some sites mandate their DBAs use. Sometimes more efficient, but you are one degree removed from Oracle. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, August 21, 2002 8:03 AM To: Multiple recipients of list ORACLE-L I fully agree. Unfortunately management insists on it. Russ -Original Message- Sent: Tuesday, August 20, 2002 9:13 PM To: Multiple recipients of list ORACLE-L Dick, There is absolutely *nothing* that SAPDBA does that a reasonably knowledgeable DBA can't do from his of her favorite toolset. ( vi, Perl and sqlplus for me :) SAP types have it drummed into their heads that the only proper way to do anything DBA work is via SAPDBA. I refuse to use it, and it just drives the SAP consultants crazy. There are many cases where a good DBA can do a much better job than SAPDBA. The tablespace reorganization is a good example. Trying to 'drop tablespace including contents' with 3500 tables is not a terribly bright way of going about it. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 08/20/2002 02:43 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re:drop tablespace including contents Russ, Your high usage of RBS was due to the updates being done to the system data dictionary. Since you were dropping a tablespace and contents the DDL statements for the individual objects (tables and indexes) needs to be done first, but I've a funny idea from practice that Oracle does not do an implicit commit in this case but instead holds on till the end. This makes dropping a tablespace with the including contents caviot very nasty. Thank GOD we never implemented SAP over here. I've heard nothing but bad about SAP and sapdba. Dick Goulet Reply Separator Author: Brooks; Russ [EMAIL PROTECTED] Date: 8/20/2002 11:13 AM Hi, This past weekend we experienced a problem on a production database, and I would like to try to determine what went wrong, how to avoid it in the future, and any better ways of dealing with it should it be encountered again. After moving some large objects out of tablespace to spread I/O, we wanted to reorganize the old tablespace to remove some fragmentation. The tool we were using, sapdba, does not readily permit you to drop the individual tables between the export and the drop tablespace including contents. Since the tablespace had over 3500 tables the drop tablespace was expected to take a long time. We also defined a large rollback segment for use this weekend, although with only maxextents of 100. When Oracle tried to allocate the 101 extent in the RBS, error messages were issued and things came to a grinding halt. sar indicated disk I/O to the new RBS, but not to any of the datafiles. We waited several hours, but the situation did not appear to change. Shutdown immediate did not work. We could alter the datafiles back online, but not the tablespace. Since it was production, the decision was made to restore to a recent backup. 1. Was the rollback activity due solely to storing and restoring DDL for the tables and indices? 2. Once the RBS was unable to extend, was the drop tablespace including contents dead? We tried to alter maxextents on the RBS, but did not get a response from the system. Was that the appropriate reaction to this problem. 3. A join of v$session and v$sql did not indicate any active SQL. How should we have monitored the progress of what we assume was rollback activity? Any way to estimate how much or how long the rollback would take? 4. If the database were shutdown during the rollback I assume the rollback would recommence when Oracle came back up. Would it start
RE: drop tablespace including contents
Mike, I've made the pitch before and will again. Wish me luck. Russ -Original Message- Sent: Wednesday, August 21, 2002 11:39 AM To: Multiple recipients of list ORACLE-L Russ, You have my sympathies. I've been managing SAP databases for the last 7 years, and the last time I let SAPDBA control a reorg was 6.95 years ago. It performs some tasks reasonably well, like datafile additions and managing CBO statistics refreshes (especially since SAP wants certain tables to be left without statistics). But for reorgs, I prefer more direct control. Perhaps you can use this SNAFU to convince management to have sapdba generate the scripts, then allow you to modified them appropriately. Mike Hand Polaroid Corp. -Original Message- Sent: Wednesday, August 21, 2002 9:03 AM To: Multiple recipients of list ORACLE-L I fully agree. Unfortunately management insists on it. Russ -Original Message- Sent: Tuesday, August 20, 2002 9:13 PM To: Multiple recipients of list ORACLE-L Dick, There is absolutely *nothing* that SAPDBA does that a reasonably knowledgeable DBA can't do from his of her favorite toolset. ( vi, Perl and sqlplus for me :) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hand, Michael T INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Brooks, Russ INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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 including contents
Yeah, that's what I do too. I just wish it wouldn't clobber the stats on the indices after I've so carefully gathered them. We have the 6.2 sapdba, so I don't think it's using dbastatc as much to control when and how it does the stats. Russ -Original Message- Sent: Wednesday, August 21, 2002 1:59 PM To: Multiple recipients of list ORACLE-L Ironically, analyzing tables is one of the jobs I leave up to SAPDBA. There are a number of tables that shouldn't be analyzed, ( ~150 on my system ) and the system knows which ones they are. Just schedule the job through transaction DB13 and forget about it. Jared paquette stephane [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 08/20/2002 09:48 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re:drop tablespace including contents At one client, one team was using SAP without a DBA, only the SAP administrator using SAPDBA. They were having poor performance. After 2-3 days they came to see me, after 5 minutes I told them that 4000 tables out of 16 000 were having no statistics at all. They analyzed during the weekend and performance was pretty good --- [EMAIL PROTECTED] a écrit : Dick, There is absolutely *nothing* that SAPDBA does that a reasonably knowledgeable DBA can't do from his of her favorite toolset. ( vi, Perl and sqlplus for me :) SAP types have it drummed into their heads that the only proper way to do anything DBA work is via SAPDBA. I refuse to use it, and it just drives the SAP consultants crazy. There are many cases where a good DBA can do a much better job than SAPDBA. The tablespace reorganization is a good example. Trying to 'drop tablespace including contents' with 3500 tables is not a terribly bright way of going about it. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 08/20/2002 02:43 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re:drop tablespace including contents Russ, Your high usage of RBS was due to the updates being done to the system data dictionary. Since you were dropping a tablespace and contents the DDL statements for the individual objects (tables and indexes) needs to be done first, but I've a funny idea from practice that Oracle does not do an implicit commit in this case but instead holds on till the end. This makes dropping a tablespace with the including contents caviot very nasty. Thank GOD we never implemented SAP over here. I've heard nothing but bad about SAP and sapdba. Dick Goulet Reply Separator Author: Brooks; Russ [EMAIL PROTECTED] Date: 8/20/2002 11:13 AM Hi, This past weekend we experienced a problem on a production database, and I would like to try to determine what went wrong, how to avoid it in the future, and any better ways of dealing with it should it be encountered again. After moving some large objects out of tablespace to spread I/O, we wanted to reorganize the old tablespace to remove some fragmentation. The tool we were using, sapdba, does not readily permit you to drop the individual tables between the export and the drop tablespace including contents. Since the tablespace had over 3500 tables the drop tablespace was expected to take a long time. We also defined a large rollback segment for use this weekend, although with only maxextents of 100. When Oracle tried to allocate the 101 extent in the RBS, error messages were issued and things came to a grinding halt. sar indicated disk I/O to the new RBS, but not to any of the datafiles. We waited several hours, but the situation did not appear to change. Shutdown immediate did not work. We could alter the datafiles back online, but not the tablespace. Since it was production, the decision was made to restore to a recent backup. 1. Was the rollback activity due solely to storing and restoring DDL for the tables and indices? 2. Once the RBS was unable to extend, was the drop tablespace including contents dead? We tried to alter maxextents on the RBS, but did not get a response from the system. Was that the appropriate reaction to this problem. 3. A join of v$session and v$sql did not indicate any active SQL. How should we have monitored the progress of what we assume was rollback activity? Any way to estimate how much or how long the rollback would take? 4. If the database were shutdown during the rollback I assume the rollback would recommence when Oracle came back up. Would it start where it left off or start from scratch. It was my impression that it is marking the header blocks as it goes, but I would like to check. Thanks, Russ Brooks !DOCTYPE HTML PUBLIC -//W3C//DTD HTML
RE: drop tablespace including contents
I had one experience with an ERP, since then I'm avoiding those contract. Developping a system is so much more interesting in my point of view. Unfortunately there is more and more ERP sold :-( --- Brooks, Russ [EMAIL PROTECTED] a écrit : Yeah, that's what I do too. I just wish it wouldn't clobber the stats on the indices after I've so carefully gathered them. We have the 6.2 sapdba, so I don't think it's using dbastatc as much to control when and how it does the stats. Russ -Original Message- Sent: Wednesday, August 21, 2002 1:59 PM To: Multiple recipients of list ORACLE-L Ironically, analyzing tables is one of the jobs I leave up to SAPDBA. There are a number of tables that shouldn't be analyzed, ( ~150 on my system ) and the system knows which ones they are. Just schedule the job through transaction DB13 and forget about it. Jared paquette stephane [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 08/20/2002 09:48 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re:drop tablespace including contents At one client, one team was using SAP without a DBA, only the SAP administrator using SAPDBA. They were having poor performance. After 2-3 days they came to see me, after 5 minutes I told them that 4000 tables out of 16 000 were having no statistics at all. They analyzed during the weekend and performance was pretty good --- [EMAIL PROTECTED] a écrit : Dick, There is absolutely *nothing* that SAPDBA does that a reasonably knowledgeable DBA can't do from his of her favorite toolset. ( vi, Perl and sqlplus for me :) SAP types have it drummed into their heads that the only proper way to do anything DBA work is via SAPDBA. I refuse to use it, and it just drives the SAP consultants crazy. There are many cases where a good DBA can do a much better job than SAPDBA. The tablespace reorganization is a good example. Trying to 'drop tablespace including contents' with 3500 tables is not a terribly bright way of going about it. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 08/20/2002 02:43 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re:drop tablespace including contents Russ, Your high usage of RBS was due to the updates being done to the system data dictionary. Since you were dropping a tablespace and contents the DDL statements for the individual objects (tables and indexes) needs to be done first, but I've a funny idea from practice that Oracle does not do an implicit commit in this case but instead holds on till the end. This makes dropping a tablespace with the including contents caviot very nasty. Thank GOD we never implemented SAP over here. I've heard nothing but bad about SAP and sapdba. Dick Goulet Reply Separator Author: Brooks; Russ [EMAIL PROTECTED] Date: 8/20/2002 11:13 AM Hi, This past weekend we experienced a problem on a production database, and I would like to try to determine what went wrong, how to avoid it in the future, and any better ways of dealing with it should it be encountered again. After moving some large objects out of tablespace to spread I/O, we wanted to reorganize the old tablespace to remove some fragmentation. The tool we were using, sapdba, does not readily permit you to drop the individual tables between the export and the drop tablespace including contents. Since the tablespace had over 3500 tables the drop tablespace was expected to take a long time. We also defined a large rollback segment for use this weekend, although with only maxextents of 100. When Oracle tried to allocate the 101 extent in the RBS, error messages were issued and things came to a grinding halt. sar indicated disk I/O to the new RBS, but not to any of the datafiles. We waited several hours, but the situation did not appear to change. Shutdown immediate did not work. We could alter the datafiles back online, but not the tablespace. Since it was production, the decision was made to restore to a recent backup. 1. Was the rollback activity due solely to storing and restoring DDL for the tables and indices? 2. Once the RBS was unable to extend, was the drop tablespace including contents dead? We tried to alter maxextents on the RBS, but did not get a response from === message truncated === = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED
RE: drop tablespace including contents
Title: Message Hi Russ, I'm experiencing a very similar situation today. A batch job aborted last night after it couldn't extend the rollback segment beyond 4GB, and I'm still waiting for the rollback to complete. Before identifying that, my symptoms were very similar to yours. Any DDL or DML against the table would hang, the cpus are very active and there is very little i/o going on. Here's is what I'm using to monitor the rollback progress, and might be helpful to you if it happens again - select a.sid,b.used_ublkfromv$session a, v$transaction bwherea.taddr = b.addr anda.username = 'PROD' ; substitute your username. Watch the USED_UBLK column. It decreases when a rollback is occuring, and the rollback will finish when it hits 0. It gives you an idea how long before the rollback will complete. HTH, Beth -Original Message-From: Brooks, Russ [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 20, 2002 3:13 PMTo: Multiple recipients of list ORACLE-LSubject: drop tablespace including contents Hi, This past weekend we experienced a problem on a production database, and I would like to try to determine what went wrong, how to avoid it in the future, and any better ways of dealing with it should it be encountered again. After moving some large objects out of tablespace to spread I/O, we wanted to reorganize the old tablespace to remove some fragmentation. The tool we were using, sapdba, does not readily permit you to drop the individual tables between the export and the drop tablespace including contents. Since the tablespace had over 3500 tables the drop tablespace was expected to take a long time. We also defined a large rollback segment for use this weekend, although with only maxextents of 100. When Oracle tried to allocate the 101 extent in the RBS, error messages were issued and things came to a grinding halt. sar indicated disk I/O to the new RBS, but not to any of the datafiles. We waited several hours, but the situation did not appear to change. Shutdown immediate did not work. We could alter the datafiles back online, but not the tablespace. Since it was production, the decision was made to restore to a recent backup. 1. Was the rollback activity due solely to storing and restoring DDL for the tables and indices? 2. Once the RBS was unable to extend, was the drop tablespace including contents dead? We tried to alter maxextents on the RBS, but did not get a response from the system. Was that the appropriate reaction to this problem. 3. A join of v$session and v$sql did not indicate any active SQL. How should we have monitored the progress of what we assume was rollback activity? Any way to estimate how much or how long the rollback would take? 4. If the database were shutdown during the rollback I assume the rollback would recommence when Oracle came back up. Would it start where it left off or start from scratch. It was my impression that it is marking the header blocks as it goes, but I would like to check.Thanks, Russ Brooks
RE: drop tablespace including contents
Beth, You could also select the info from the SM$TS_USED or SM$TS_FREE table as SYS. the table desc is TABLESPACE_NAME, BYTES. You should see the bytes change as the work progresses. I got this tip from OWS when I saw chasing a SMON at 100%cpu after a crash. Ron ROR mô¿ôm [EMAIL PROTECTED] 08/20/02 04:48PM Hi Russ, I'm experiencing a very similar situation today. A batch job aborted last night after it couldn't extend the rollback segment beyond 4GB, and I'm still waiting for the rollback to complete. Before identifying that, my symptoms were very similar to yours. Any DDL or DML against the table would hang, the cpus are very active and there is very little i/o going on. Here's is what I'm using to monitor the rollback progress, and might be helpful to you if it happens again - select a.sid,b.used_ublk from v$session a, v$transaction b where a.taddr = b.addr and a.username = 'PROD' ; substitute your username. Watch the USED_UBLK column. It decreases when a rollback is occuring, and the rollback will finish when it hits 0. It gives you an idea how long before the rollback will complete. HTH, Beth -Original Message- Sent: Tuesday, August 20, 2002 3:13 PM To: Multiple recipients of list ORACLE-L Hi, This past weekend we experienced a problem on a production database, and I would like to try to determine what went wrong, how to avoid it in the future, and any better ways of dealing with it should it be encountered again. After moving some large objects out of tablespace to spread I/O, we wanted to reorganize the old tablespace to remove some fragmentation. The tool we were using, sapdba, does not readily permit you to drop the individual tables between the export and the drop tablespace including contents. Since the tablespace had over 3500 tables the drop tablespace was expected to take a long time. We also defined a large rollback segment for use this weekend, although with only maxextents of 100. When Oracle tried to allocate the 101 extent in the RBS, error messages were issued and things came to a grinding halt. sar indicated disk I/O to the new RBS, but not to any of the datafiles. We waited several hours, but the situation did not appear to change. Shutdown immediate did not work. We could alter the datafiles back online, but not the tablespace. Since it was production, the decision was made to restore to a recent backup. 1. Was the rollback activity due solely to storing and restoring DDL for the tables and indices? 2. Once the RBS was unable to extend, was the drop tablespace including contents dead? We tried to alter maxextents on the RBS, but did not get a response from the system. Was that the appropriate reaction to this problem. 3. A join of v$session and v$sql did not indicate any active SQL. How should we have monitored the progress of what we assume was rollback activity? Any way to estimate how much or how long the rollback would take? 4. If the database were shutdown during the rollback I assume the rollback would recommence when Oracle came back up. Would it start where it left off or start from scratch. It was my impression that it is marking the header blocks as it goes, but I would like to check. Thanks, Russ Brooks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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 including contents
Title: Message Hi Beth, Sounds similar, unfortunately. Thanks for the SQL. I'll keep it just in case. Good luck with your situation. Russ -Original Message-From: Seefelt, Beth [mailto:[EMAIL PROTECTED]]Sent: Tuesday, August 20, 2002 4:49 PMTo: Multiple recipients of list ORACLE-LSubject: RE: drop tablespace including contents Hi Russ, I'm experiencing a very similar situation today. A batch job aborted last night after it couldn't extend the rollback segment beyond 4GB, and I'm still waiting for the rollback to complete. Before identifying that, my symptoms were very similar to yours. Any DDL or DML against the table would hang, the cpus are very active and there is very little i/o going on. Here's is what I'm using to monitor the rollback progress, and might be helpful to you if it happens again - select a.sid,b.used_ublkfromv$session a, v$transaction bwherea.taddr = b.addr anda.username = 'PROD' ; substitute your username. Watch the USED_UBLK column. It decreases when a rollback is occuring, and the rollback will finish when it hits 0. It gives you an idea how long before the rollback will complete. HTH, Beth -Original Message-From: Brooks, Russ [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 20, 2002 3:13 PMTo: Multiple recipients of list ORACLE-LSubject: drop tablespace including contents Hi, This past weekend we experienced a problem on a production database, and I would like to try to determine what went wrong, how to avoid it in the future, and any better ways of dealing with it should it be encountered again. After moving some large objects out of tablespace to spread I/O, we wanted to reorganize the old tablespace to remove some fragmentation. The tool we were using, sapdba, does not readily permit you to drop the individual tables between the export and the drop tablespace including contents. Since the tablespace had over 3500 tables the drop tablespace was expected to take a long time. We also defined a large rollback segment for use this weekend, although with only maxextents of 100. When Oracle tried to allocate the 101 extent in the RBS, error messages were issued and things came to a grinding halt. sar indicated disk I/O to the new RBS, but not to any of the datafiles. We waited several hours, but the situation did not appear to change. Shutdown immediate did not work. We could alter the datafiles back online, but not the tablespace. Since it was production, the decision was made to restore to a recent backup. 1. Was the rollback activity due solely to storing and restoring DDL for the tables and indices? 2. Once the RBS was unable to extend, was the drop tablespace including contents dead? We tried to alter maxextents on the RBS, but did not get a response from the system. Was that the appropriate reaction to this problem. 3. A join of v$session and v$sql did not indicate any active SQL. How should we have monitored the progress of what we assume was rollback activity? Any way to estimate how much or how long the rollback would take? 4. If the database were shutdown during the rollback I assume the rollback would recommence when Oracle came back up. Would it start where it left off or start from scratch. It was my impression that it is marking the header blocks as it goes, but I would like to check.Thanks, Russ Brooks
Recall: drop tablespace including contents
Brooks, Russ would like to recall the message, drop tablespace including contents. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Brooks, Russ INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Recall: drop tablespace including contents
Brooks, Russ, your request to recall the message, drop tablespace including contents has been DENIED. Brooks, Russ wrote: Brooks, Russ would like to recall the message, drop tablespace including contents. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
DROP TABLESPACE TAKES so much time
Hi Drop tablespace including contents command is taking so much of time. Is any specific reason? Thx -Seema _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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 TAKES so much time
It can take fffooorreevvveer if you have a fragmented dictionary managed tablespace (lot's and lot's of extents, free or used). Oracle is spending all it's time cleaning up the sys.uet$ Brian P. MacLean Oracle DBA, OCP8i Seema Singh oracledbam@ho To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] tmail.com cc: Sent by: Subject: DROP TABLESPACE TAKES so much time [EMAIL PROTECTED] om 04/01/02 10:42 AM Please respond to ORACLE-L Hi Drop tablespace including contents command is taking so much of time. Is any specific reason? Thx -Seema _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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 TAKES so much time
well, the number of objects in that tablespace and the number of extents for each of these objects could definitely explain the amount of time. --- Seema Singh [EMAIL PROTECTED] wrote: Hi Drop tablespace including contents command is taking so much of time. Is any specific reason? Thx -Seema _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). __ Do You Yahoo!? Yahoo! Greetings - send holiday greetings for Easter, Passover http://greetings.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
What is the fastest way to drop tablespace ? - urgent please
Dear gurus ! What is the fastest way to drop tablespace , please ? I issued drop tablespace including contents command and it runs for some 5 hours already . The tablespace has 6 datafiles totalling ~ 13GB space. While trying to look why does it take too long , i discovered that there is a heavy activity going on under user SYS. I see a lot of rollback generated , and the most common DML is delete from fet$ . . Ok, i understand that the rollback is generated for data dictionary changes , space must be reclaimed from fet$ , since the datafiles that comprise my tablespace are going offline , but why in the world does it take half a day ? And how can i speed it up , please ? Thanks a lot ! DBAndrey * 03-9254520 * 053-464562 * mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: What is the fastest way to drop tablespace ? - urgent please
since u wanna drop this anyways .. why not simply offline immediate the tblspc and then drop it? deepak --- Andrey Bronfin [EMAIL PROTECTED] wrote: Dear gurus ! What is the fastest way to drop tablespace , please ? I issued drop tablespace including contents command and it runs for some 5 hours already . The tablespace has 6 datafiles totalling ~ 13GB space. While trying to look why does it take too long , i discovered that there is a heavy activity going on under user SYS. I see a lot of rollback generated , and the most common DML is delete from fet$ . . Ok, i understand that the rollback is generated for data dictionary changes , space must be reclaimed from fet$ , since the datafiles that comprise my tablespace are going offline , but why in the world does it take half a day ? And how can i speed it up , please ? Thanks a lot ! DBAndrey * 03-9254520 * 053-464562 * mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deepak Thapliyal INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
