手工清除回滚段的几种方式

《手工清除回滚段的几种方式》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。

1、手工清除回滚段的几种方式一、概要本文已经发表在个人博客http://www.killdb.com/?p=196在某些情况下，我们需要手动去清除一些有问题的回滚段，如果该回滚段中包含活动事务，那么使用正常的方式将无法drop，所以此时你dropundotablespace也将失败。可能就会遇到如下的错误：SQL>droptablespaceundotbs1includingcontentsanddatafiles;droptablespaceundotbs1includingcontentsanddat

2、afiles*ERRORatline1:ORA-01561:failedtoremoveallobjectsinthetablespacespecified二、手工清除回滚段的几种方式1.使用隐含参数SQL>showparameterundoNAMETYPEVALUE-----------------------------------------------------------------------------undo_managementstringAUTOundo_retentionint

3、eger900undo_tablespacestringUNDOTBS1SQL>createundotablespaceundotbs2datafile'/oracle/product/oradata/roger/undotbs2.dbf'2size50mautoextendoff;Tablespacecreated.SQL>createundotablespaceundotbs3datafile'/oracle/product/oradata/roger/undotbs3.dbf'2size50ma

4、utoextendoff;Tablespacecreated.SQL>SQL>connroger/rogerConnected.SQL>createtableht_01asselect*fromdba_objectswhererownum<10;Tablecreated.SQL>deletefromht_01whererownum<5;4rowsdeleted.SQL>-----不提交SQL>selectowner,segment_name,SEGMENT_ID,FILE_ID,BLOCK_ID,ST

5、ATUS2,tablespace_namefromdba_rollback_segs;OWNERSEGMENT_NAMESEGMENT_IDFILE_IDBLOCK_IDSTATUSTABLESPACE_NAME-------------------------------------------------------------------------SYSSYSTEM019ONLINESYSTEMPUBLIC_SYSSMU1$129ONLINEUNDOTBS1PUBLIC_SYSSMU2$222

6、5ONLINEUNDOTBS1PUBLIC_SYSSMU3$3241ONLINEUNDOTBS1PUBLIC_SYSSMU4$4257ONLINEUNDOTBS1PUBLIC_SYSSMU5$5273ONLINEUNDOTBS1PUBLIC_SYSSMU6$6289ONLINEUNDOTBS1PUBLIC_SYSSMU7$72105ONLINEUNDOTBS1PUBLIC_SYSSMU8$82121ONLINEUNDOTBS1PUBLIC_SYSSMU9$92137ONLINEUNDOTBS1PUBL

7、IC_SYSSMU10$102153ONLINEUNDOTBS1PUBLIC_SYSSMU11$1169OFFLINEUNDOTBS2PUBLIC_SYSSMU12$12625OFFLINEUNDOTBS2PUBLIC_SYSSMU13$13641OFFLINEUNDOTBS2PUBLIC_SYSSMU14$14657OFFLINEUNDOTBS2PUBLIC_SYSSMU15$15673OFFLINEUNDOTBS2PUBLIC_SYSSMU16$16689OFFLINEUNDOTBS2PUBLIC

8、_SYSSMU17$176105OFFLINEUNDOTBS2PUBLIC_SYSSMU18$186121OFFLINEUNDOTBS2PUBLIC_SYSSMU19$196137OFFLINEUNDOTBS2PUBLIC_SYSSMU20$206153OFFLINEUNDOTBS2PUBLIC_SYSSMU21$2179OFFLINEUNDOTBS3PUBLIC_SYSSMU22$22725OFFLINEUNDOTBS3PUBLIC_SYSSMU23$