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

1、 手工清除回滚段的几种方式一、 概要本文已经发表在个人博客在某些情况下，我们需要手动去清除一些有问题的回滚段，如果该回滚段中包含活动事务，那么使用正常的方式将无法drop，所以此时你drop undo tablespace 也将失败。可能就会遇到如下的错误：SQL> drop tablespace undotbs1 including contents and datafiles;drop tablespace undotbs1 including contents and datafiles*ERROR at line 1:ORA-01561: failed to remove all

2、 objects in the tablespace specified二、手工清除回滚段的几种方式1. 使用隐含参数SQL> show parameter undoNAME TYPE VALUE- - -undo_management string AUTOundo_retention integer 900undo_tablespace string UNDOTBS1SQL> create undo tablespace undotbs2 datafile '/oracle/product/oradata/roger/undotbs2.dbf' 2 size 5

3、0m autoextend off;Tablespace created.SQL> create undo tablespace undotbs3 datafile '/oracle/product/oradata/roger/undotbs3.dbf' 2 size 50m autoextend off;Tablespace created.SQL> SQL> conn roger/rogerConnected.SQL> create table ht_01 as select * from dba_objects where rownum <1

4、0;Table created.SQL> delete from ht_01 where rownum <5;4 rows deleted.SQL> -不提交SQL> select owner,segment_name,SEGMENT_ID,FILE_ID,BLOCK_ID,STATUS 2 ,tablespace_name from dba_rollback_segs;OWNER SEGMENT_NAME SEGMENT_ID FILE_ID BLOCK_ID STATUS TABLESPACE_NAME- - - - - - -SYS SYSTEM 0 1 9 ON

5、LINE SYSTEMPUBLIC _SYSSMU1$ 1 2 9 ONLINE UNDOTBS1PUBLIC _SYSSMU2$ 2 2 25 ONLINE UNDOTBS1PUBLIC _SYSSMU3$ 3 2 41 ONLINE UNDOTBS1PUBLIC _SYSSMU4$ 4 2 57 ONLINE UNDOTBS1PUBLIC _SYSSMU5$ 5 2 73 ONLINE UNDOTBS1PUBLIC _SYSSMU6$ 6 2 89 ONLINE UNDOTBS1PUBLIC _SYSSMU7$ 7 2 105 ONLINE UNDOTBS1PUBLIC _SYSSMU8$

6、 8 2 121 ONLINE UNDOTBS1PUBLIC _SYSSMU9$ 9 2 137 ONLINE UNDOTBS1PUBLIC _SYSSMU10$ 10 2 153 ONLINE UNDOTBS1PUBLIC _SYSSMU11$ 11 6 9 OFFLINE UNDOTBS2PUBLIC _SYSSMU12$ 12 6 25 OFFLINE UNDOTBS2PUBLIC _SYSSMU13$ 13 6 41 OFFLINE UNDOTBS2PUBLIC _SYSSMU14$ 14 6 57 OFFLINE UNDOTBS2PUBLIC _SYSSMU15$ 15 6 73 O

7、FFLINE UNDOTBS2PUBLIC _SYSSMU16$ 16 6 89 OFFLINE UNDOTBS2PUBLIC _SYSSMU17$ 17 6 105 OFFLINE UNDOTBS2PUBLIC _SYSSMU18$ 18 6 121 OFFLINE UNDOTBS2PUBLIC _SYSSMU19$ 19 6 137 OFFLINE UNDOTBS2PUBLIC _SYSSMU20$ 20 6 153 OFFLINE UNDOTBS2PUBLIC _SYSSMU21$ 21 7 9 OFFLINE UNDOTBS3PUBLIC _SYSSMU22$ 22 7 25 OFFL

8、INE UNDOTBS3PUBLIC _SYSSMU23$ 23 7 41 OFFLINE UNDOTBS3PUBLIC _SYSSMU24$ 24 7 57 OFFLINE UNDOTBS3PUBLIC _SYSSMU25$ 25 7 73 OFFLINE UNDOTBS3PUBLIC _SYSSMU26$ 26 7 89 OFFLINE UNDOTBS3PUBLIC _SYSSMU27$ 27 7 105 OFFLINE UNDOTBS3PUBLIC _SYSSMU28$ 28 7 121 OFFLINE UNDOTBS3PUBLIC _SYSSMU29$ 29 7 137 OFFLINE

9、 UNDOTBS3PUBLIC _SYSSMU30$ 30 7 153 OFFLINE UNDOTBS331 rows selected.SQL> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction; XIDUSN XIDSLOT XIDSQN UBABLK UBAFIL UBAREC- - - - - - 9 13 299 439 2 47SQL> select usn,name from v$rollname where usn=9; USN NAME- - 9 _SYSSMU9$SQL>

10、; SQL> c/file_name/file_name,tablespace_name 1* select file_id,file_name,tablespace_name from dba_data_files order by 1SQL> / FILE_ID FILE_NAME TABLESPACE_NAME- - - 1 /oracle/product/oradata/roger/system01.dbf SYSTEM 2 /oracle/product/oradata/roger/undotbs01.dbf UNDOTBS1 3 /oracle/product/orad

11、ata/roger/sysaux01.dbf SYSAUX 4 /oracle/product/oradata/roger/users01.dbf USERS 5 /oracle/product/oradata/roger/roger01.dbf ROGER 6 /oracle/product/oradata/roger/undotbs2.dbf UNDOTBS2 7 /oracle/product/oradata/roger/undotbs3.dbf UNDOTBS3 7 rows selected. SQL> SQL> show parameter undo NAME TYPE

12、 VALUE- - -undo_management string AUTOundo_retention integer 900undo_tablespace string UNDOTBS1SQL> alter system set undo_tablespace=undotbs2;System altered.SQL> alter database datafile 2 offline;Database altered.SQL> SQL> shutdown abort;ORACLE instance shut down.SQL> SQL> SQL>

13、startupORACLE instance started.Total System Global Area 167772160 bytesFixed Size 1266392 bytesVariable Size 104860968 bytesDatabase Buffers 58720256 bytesRedo Buffers 2924544 bytesDatabase mounted.Database opened.SQL> drop tablespace undotbs1 including contents and datafiles;drop tablespace undo

14、tbs1 including contents and datafiles*ERROR at line 1:ORA-01548: active rollback segment '_SYSSMU9$' found, terminate dropping tablespaceSQL> -我们看到报错了 意思是说该回滚段中还有活动事务SQL> conn roger/rogerConnected.SQL> show parameter undoNAME TYPE VALUE- - -_collect_undo_stats boolean TRUE_gc_dissol

15、ve_undo_affinity boolean FALSE_gc_initiate_undo_affinity boolean TRUE_gc_undo_affinity boolean TRUE_gc_undo_affinity_locks boolean TRUE_in_memory_undo boolean TRUE_kcl_undo_grouping integer 32_kcl_undo_locks integer 128_optimizer_undo_changes boolean FALSE_optimizer_undo_cost_change string 10.2.0.4_

16、smon_undo_seg_rescan_limit integer 10_undo_autotune boolean TRUE_undo_debug_mode integer 0_undo_debug_usage integer 0_verify_undo_quota boolean FALSEundo_management string AUTOundo_retention integer 900undo_tablespace string UNDOTBS2SQL> SQL> alter system set "_smu_debug_mode" = 4;Sy

17、stem altered.SQL> alter rollback segment "_SYSSMU9$" offline;alter rollback segment "_SYSSMU9$" offline*ERROR at line 1:ORA-01598: rollback segment '_SYSSMU9$' is not onlineSQL> drop rollback segment "_SYSSMU9$"drop rollback segment "_SYSSMU9$"*E

18、RROR at line 1:ORA-01545: rollback segment '_SYSSMU9$' specified not availableSQL> select owner,segment_name,SEGMENT_ID,FILE_ID,BLOCK_ID,STATUS 2 from dba_rollback_segs where segment_name='_SYSSMU9$'OWNER SEGMENT_NAME SEGMENT_ID FILE_ID BLOCK_ID STATUS- - - - - -PUBLIC _SYSSMU9$ 9

19、 2 137 NEEDS RECOVERYSQL> 用如下隐含参数：_offline_rollback_segments=(_SYSSMU9$)_corrupted_rollback_segments=(_SYSSMU9$)SQL> startup mount pfile='/oracle/pfile.ora'ORACLE instance started.Total System Global Area 167772160 bytesFixed Size 1266392 bytesVariable Size 104860968 bytesDatabase Buff

20、ers 58720256 bytesRedo Buffers 2924544 bytesDatabase mounted.SQL> alter database open;Database altered.SQL> select owner,segment_name,SEGMENT_ID,FILE_ID,BLOCK_ID,STATUS 2 from dba_rollback_segs where segment_name='_SYSSMU9$'OWNER SEGMENT_NAME SEGMENT_ID FILE_ID BLOCK_ID STATUS- - - - -

21、 -PUBLIC _SYSSMU9$ 9 2 137 NEEDS RECOVERYSQL> drop rollback segment "_SYSSMU9$"Rollback segment dropped.SQL>SQL> drop tablespace undotbs1 including contents and datafiles;Tablespace dropped.SQL> SQL> select count(*) from ht_01; COUNT(*)- 5 -数据丢失这里可能有人会问上面的_undo_debug_mode参数为

22、啥不管用？其实不是没效果，是针对这种情况下，没用而已，对于自动undo管理模式，可以使用该参数来手工删除某个回滚段，前提是该回滚段无活动事务。2. 通过更改数据字典表 来删除回滚段和undo tablespaceSQL> show parameter undoNAME TYPE VALUE- - -undo_management string AUTOundo_retention integer 900undo_tablespace string UNDOTBS3SQL> conn roger/rogerConnected.SQL> select count(*) from

23、ht_01; COUNT(*)- 3SQL> delete from ht_01 where rownum <2;1 row deleted.SQL> -不提交SQL> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction; XIDUSN XIDSLOT XIDSQN UBABLK UBAFIL UBAREC- - - - - - 2 5 310 345 7 6SQL> select usn,name from v$rollname where usn=2; USN NAME- -

24、 2 _SYSSMU2$SQL> alter system set undo_tablespace=undotbs4;System altered.SQL> alter database datafile 7 offline;Database altered.SQL> SQL> shutdown abort;ORACLE instance shut down.SQL> startupORACLE instance started.Total System Global Area 167772160 bytesFixed Size 1266392 bytesVari

25、able Size 104860968 bytesDatabase Buffers 58720256 bytesRedo Buffers 2924544 bytesDatabase mounted.Database opened.SQL> drop tablespace undotbs3 including contents and datafiles;drop tablespace undotbs3 including contents and datafiles*ERROR at line 1:ORA-01548: active rollback segment '_SYSS

26、MU2$' found, terminate dropping tablespaceSQL> select ts# from ts$ where name='UNDOTBS3' TS#- 7SQL> select file#,block#,TYPE#,TS# from seg$ where ts#=7; FILE# BLOCK# TYPE# TS#- - - - 7 9 10 7 7 25 10 7 7 41 10 7 7 57 10 7 7 73 10 7 7 89 10 7 7 105 10 7 7 121 10 7 7 137 10 7 7 153 1

27、0 7 7 265 3 7 7 281 10 712 rows selected.SQL> select owner,segment_name,SEGMENT_ID,FILE_ID,BLOCK_ID,STATUS 2 from dba_rollback_segs where file_id=7;OWNER SEGMENT_NAME SEGMENT_ID FILE_ID BLOCK_ID STATUS- - - - - -PUBLIC _SYSSMU2$ 2 7 281 NEEDS RECOVERYPUBLIC _SYSSMU21$ 21 7 9 OFFLINEPUBLIC _SYSSMU

28、22$ 22 7 25 OFFLINEPUBLIC _SYSSMU23$ 23 7 41 OFFLINEPUBLIC _SYSSMU24$ 24 7 57 OFFLINEPUBLIC _SYSSMU25$ 25 7 73 OFFLINEPUBLIC _SYSSMU26$ 26 7 89 OFFLINEPUBLIC _SYSSMU27$ 27 7 105 OFFLINEPUBLIC _SYSSMU28$ 28 7 121 OFFLINEPUBLIC _SYSSMU29$ 29 7 137 OFFLINEPUBLIC _SYSSMU30$ 30 7 153 OFFLINE11 rows selec

29、ted.SQL> SQL> update seg$ set type# = 3 where ts#=7 and file#=7 and BLOCK#=281;1 row updated.SQL> commit;Commit complete.SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startupORACLE instance started.Total System Global Area 167772160 bytesFi

30、xed Size 1266392 bytesVariable Size 104860968 bytesDatabase Buffers 58720256 bytesRedo Buffers 2924544 bytesDatabase mounted.Database opened. SQL> select US# ,NAME,FILE#,BLOCK# from undo$ where file#=7; US# NAME FILE# BLOCK#- - - - 1 _SYSSMU1$ 7 265 2 _SYSSMU2$ 7 281 21 _SYSSMU21$ 7 9 22 _SYSSMU2

31、2$ 7 25 23 _SYSSMU23$ 7 41 24 _SYSSMU24$ 7 57 25 _SYSSMU25$ 7 73 26 _SYSSMU26$ 7 89 27 _SYSSMU27$ 7 105 28 _SYSSMU28$ 7 121 29 _SYSSMU29$ 7 137 30 _SYSSMU30$ 7 153 12 rows selected. SQL> SQL> delete from undo$ where ts#=7 and US#=2;1 row deleted.SQL> delete from seg$ where ts#=7 and file#=7

32、 and block#=281;1 row deleted.SQL> SQL> commit;Commit complete.SQL> drop rollback segment "_SYSSMU2$"drop rollback segment "_SYSSMU2$"*ERROR at line 1:ORA-01545: rollback segment '_SYSSMU2$' specified not available由于我们已经从几个数据字典表中将该段清除了，所以需要用包检查下SQL> execute hc

33、heck.fullPL/SQL procedure successfully completed.SQL> drop rollback segment "_SYSSMU2$"Rollback segment dropped.SQL> SQL> drop tablespace undotbs3 including contents and datafiles;drop tablespace undotbs3 including contents and datafiles*ERROR at line 1:ORA-01561: failed to remove

34、 all objects in the tablespace specified对于该错误，处理起来就非常容易了，如下：SQL> update seg$ set type# = 3 where ts#=7;11 rows updated.SQL> commit;Commit complete.SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startupORACLE instance started.Total System Global

35、 Area 167772160 bytesFixed Size 1266392 bytesVariable Size 104860968 bytesDatabase Buffers 58720256 bytesRedo Buffers 2924544 bytesDatabase mounted.Database opened.SQL> drop tablespace undotbs3 including contents and datafiles;Tablespace dropped.SQL> -Drop Tablespace 成功简单的总结下，其实我们只要通过如下步骤就能轻易的

36、删除：1. 将回滚段更改为临时段2. 重启实例3. 从seg$中删除记录4. 从undo$中删除记录 需要注意一下的是，如果不使用hcheck.full 那么直接drop tablespace可能遇到如下错误：SQL> drop tablespace undotbs2 including contents and datafiles;drop tablespace undotbs2 including contents and datafiles*ERROR at line 1:ORA-00600: internal error code, arguments: ktssdrp1, 5,

37、 6, 25, , , , 3. 使用bbed 修改元数据SQL> purge recyclebin;Recyclebin purged.SQL> create table ht01 as select owner,object_name,object_id 2 from dba_objects where object_id <100;Table created.SQL> select count(*) from ht01; COUNT(*)- 98SQL> delete from ht01 where object_id <10;8 rows delet

38、ed.SQL> -不提交SQL> show parameter undoNAME TYPE VALUE- - -undo_management string AUTOundo_retention integer 900undo_tablespace string UNDOTBS4SQL> select owner,segment_name,SEGMENT_ID,FILE_ID,BLOCK_ID,STATUS 2 from dba_rollback_segs where tablespace_name='UNDOTBS4'OWNER SEGMENT_NAME S

39、EGMENT_ID FILE_ID BLOCK_ID STATUS- - - - - -PUBLIC _SYSSMU1$ 1 2 169 ONLINEPUBLIC _SYSSMU2$ 2 2 185 ONLINEPUBLIC _SYSSMU3$ 3 2 9 OFFLINEPUBLIC _SYSSMU4$ 4 2 25 OFFLINEPUBLIC _SYSSMU5$ 5 2 41 OFFLINEPUBLIC _SYSSMU6$ 6 2 57 OFFLINEPUBLIC _SYSSMU7$ 7 2 73 OFFLINEPUBLIC _SYSSMU8$ 8 2 89 OFFLINEPUBLIC _SYSSMU10$ 10 2 105 OFFLINEPUBLIC _SYSSMU11$ 11 2 121 OFFLINEPUBLIC _SYSSMU12$ 12 2 137 OFFLINEPUBLIC _SYSS