資源描述：
《手工清除回滾段的幾種方式》由會員上傳分享，免費在線閱讀，更多相關內容在行業(yè)資料-天天文庫。
1、手工清除回滾段的幾種方式一、概要本文已經發(fā)表在個人博客http://www.killdb.com/?p=196在某些情況下，我們需要手動去清除一些有問題的回滾段，如果該回滾段中包含活動事務，那么使用正常的方式將無法drop，所以此時你dropundotablespace也將失敗?？赡芫蜁龅饺缦碌腻e誤：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$
