Step 1. Symptoms when trying to drop a undo tablespace<\/p>\n
SQL> drop tablespace undotbs1;\r\ndrop tablespace undotbs1\r\n*\r\nERROR at line 1:\r\nORA-01548: active rollback segment '_SYSSMU1_1282527427$' found, terminate dropping tablespace<\/pre>\nStep 2. File which rollback segments to remove<\/p>\n
SQL> SELECT segment_name, tablespace_name, status\r\n FROM sys.dba_rollback_segs; 2\r\nSEGMENT_NAME TABLESPACE_NAME STATUS\r\n------------------------------ ------------------------------ ----------------\r\nSYSTEM SYSTEM ONLINE\r\n_SYSSMU3_1282527427$ UNDOTBS1 NEEDS RECOVERY\r\n_SYSSMU4_1282527427$ UNDOTBS1 NEEDS RECOVERY\r\n_SYSSMU5_1282527427$ UNDOTBS1 NEEDS RECOVERY\r\n_SYSSMU6_1282527427$ UNDOTBS1 NEEDS RECOVERY\r\n_SYSSMU7_1282527427$ UNDOTBS1 NEEDS RECOVERY\r\n_SYSSMU8_1282527427$ UNDOTBS1 NEEDS RECOVERY\r\n_SYSSMU9_1282527427$ UNDOTBS1 NEEDS RECOVERY\r\n_SYSSMU10_1282527427$ UNDOTBS1 NEEDS RECOVERY\r\n_SYSSMU11_1282528448$ UNDOTBS1 NEEDS RECOVERY\r\n_SYSSMU12_1282528448$ UNDOTBS1 NEEDS RECOVERY\r\nSEGMENT_NAME TABLESPACE_NAME STATUS\r\n------------------------------ ------------------------------ ----------------\r\n_SYSSMU13_1282528448$ UNDOTBS1 OFFLINE\r\n_SYSSMU14_1282528448$ UNDOTBS1 OFFLINE\r\n_SYSSMU15_1282528448$ UNDOTBS1 OFFLINE\r\n_SYSSMU16_1282528448$ UNDOTBS1 OFFLINE\r\n_SYSSMU17_1282528448$ UNDOTBS1 OFFLINE\r\n_SYSSMU18_1287968607$ UNDOTBS1 OFFLINE\r\n_SYSSMU19_1368756281$ UNDOTBS1 OFFLINE\r\n_SYSSMU20_1368756281$ UNDOTBS1 OFFLINE\r\n_SYSSMU21_1368756883$ UNDOTBS1 OFFLINE<\/pre>\nStep 3. Create a pfile from spfile and modify the contents<\/p>\n
*.undo_management='MANUAL'\r\n*.undo_retention=3600\r\n*.undo_tablespace='UNDOTBS1'\r\n*._offline_rollback_segments=(_SYSSMU3_1282527427$,_SYSSMU4_1282527427$,_SYSSMU5_1282527427$,_SYSSMU6_1282527427$,_SYSSMU7_1282527427$,_SYSSMU8_1282527427$,_1282527427$,_SYSSMU10_1282527427$,_SYSSMU11_1282528448$,_SYSSMU12_1282528448$)\r\n\r\nStep 4. Drop all the relevant rollback segments\r\nSQL> drop rollback segment \"_SYSSMU6_1282527427$\";\r\ndrop rollback segment \"_SYSSMU7_1282527427$\";\r\ndrop rollback segment \"_SYSSMU8_1282527427$\";\r\nRollback segment dropped.\r\nSQL>\r\nRollback segment dropped.\r\nSQL>\r\nRollback segment dropped.\r\nSQL> drop rollback segment \"_SYSSMU9_1282527427$\";\r\nRollback segment dropped.\r\nSQL> drop rollback segment \"_SYSSMU10_1282527427$\";\r\nRollback segment dropped.\r\nSQL> drop rollback segment \"_SYSSMU11_1282528448$\";\r\nRollback segment dropped.\r\nSQL> drop rollback segment \"_SYSSMU12_1282528448$\";\r\nRollback segment dropped.\r\n\r\nStep 5. Drop the tablespace in question, then create a new copy\r\nSQL> drop tablespace UNDOTBS1 including contents AND DATAFILES;\r\nTablespace dropped.\r\nSQL> create undo tablespace undotbs3 datafile '\/databases\/TGFLXRU\/data02\/undotbs03.dbf' size 5G;\r\n\r\nStep 6. Drop the tablespace in question, then create a new copy\r\nRecreate the spfile with the following and restart using spfile\r\n \r\n*.undo_management='AUTO'\r\n*.undo_retention=3600\r\n*.undo_tablespace='UNDOTBS3'<\/pre>\n","protected":false},"excerpt":{"rendered":"Step 1. Symptoms when trying to drop a undo tablespace SQL> drop tablespace undotbs1; drop tablespace undotbs1 * ERROR at line 1: ORA-01548: active rollback segment ‘_SYSSMU1_1282527427$’ found, terminate dropping tablespace Step 2. File which rollback segments to remove SQL> SELECT segment_name, tablespace_name, status FROM sys.dba_rollback_segs; 2 SEGMENT_NAME TABLESPACE_NAME STATUS —————————— —————————— —————- SYSTEM SYSTEM … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[52],"class_list":["post-188","post","type-post","status-publish","format-standard","hentry","category-oracle-database","tag-undo"],"_links":{"self":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/188","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/comments?post=188"}],"version-history":[{"count":1,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/188\/revisions"}],"predecessor-version":[{"id":189,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/posts\/188\/revisions\/189"}],"wp:attachment":[{"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/media?parent=188"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/categories?post=188"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.mrmarkyoung.com\/oracle\/wp-json\/wp\/v2\/tags?post=188"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}