解决回滚表空间太大或回滚表空间丢失情况（下）

接上篇

一切顺利的话，以上步骤就可以实现减小回滚表空间大小的目的。但是可能在第7步出现：
ORA-01548: 已找到活动回退段'_SYSSMU1$'，终止删除表空间 问题。


解决方案：
   这个一般是由于原回滚表空间还存在些回滚段未被回收（这个说法待确定）
1. 查看原回滚表空间状态
  select file#,status from v$datafile;

  FILE# STATUS
---------- -------
         1 SYSTEM
         2 ONLINE
         3 RECOVER  

发现原回滚表空间出在recover状态。这个是因为我们已经将表空间文件删除了

2. 查看回滚段信息
  segment_name,status,tablespace_name from dba_rollback_segs;

SEGMENT_NAME                   STATUS           TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
SYSTEM                         ONLINE           SYSTEM
_SYSSMU35_1277269112$          OFFLINE          UNDOTBS1
_SYSSMU34_1277269112$          OFFLINE          UNDOTBS1
_SYSSMU33_1277269112$          OFFLINE          UNDOTBS1
_SYSSMU32_1277269111$          OFFLINE          UNDOTBS1
_SYSSMU31_1277269111$          OFFLINE          UNDOTBS1
_SYSSMU30_1277269111$          OFFLINE          UNDOTBS1
_SYSSMU29_1277269111$          OFFLINE          UNDOTBS1
_SYSSMU28_1277269111$          OFFLINE          UNDOTBS1
_SYSSMU27_1277269111$          OFFLINE          UNDOTBS1
_SYSSMU26_1277269111$          NEEDS RECOVERY   UNDOTBS1

SEGMENT_NAME                   STATUS           TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
_SYSSMU25_1277269111$          NEEDS RECOVERY   UNDOTBS1
_SYSSMU24_1277269111$          NEEDS RECOVERY   UNDOTBS1
_SYSSMU23_1277269111$          NEEDS RECOVERY   UNDOTBS1
_SYSSMU22_1277269111$          NEEDS RECOVERY   UNDOTBS1
_SYSSMU21_1277119714$          NEEDS RECOVERY   UNDOTBS1
_SYSSMU20_1277119714$          NEEDS RECOVERY   UNDOTBS1
_SYSSMU19_1273814895$          NEEDS RECOVERY   UNDOTBS1
_SYSSMU18_1269840500$          NEEDS RECOVERY   UNDOTBS1
_SYSSMU17_1269840500$          NEEDS RECOVERY   UNDOTBS1
_SYSSMU16_1269840500$          NEEDS RECOVERY   UNDOTBS1
_SYSSMU15_1269840500$          NEEDS RECOVERY   UNDOTBS1

发现很多回滚段的数据需要RECOVERY， 这个时候需要改动oracle启动文件
sql>cd /opt/oracle/product/11.1/dbs
sql> vi initSID.ora
在末尾添加：
undo_management=manual
undo_retention=10800
undo_tablespace=undotbs2
_CORRUPTED_ROLLBACK_SEGMENTS =(_SYSSMU26_1277269111$,_SYSSMU25_1277269111$,_SYSSMU24_1277269111$,_SYSSMU23_1277269111$,_SYSSMU22_1277269111$,_SYSSMU21_1277119714$,_SYSSMU20_1277119714$,_SYSSMU19_1273814895$,_SYSSMU18_1269840500$,_SYSSMU17_1269840500$,_SYSSMU16_1269840500$,_SYSSMU15_1269840500$,_SYSSMU14_1269840500$,_SYSSMU13_1269840500$,_SYSSMU12_1269840500$,_SYSSMU11_1269840500$,_SYSSMU10_1259671438$,_SYSSMU9_1259671438$,_SYSSMU8_1259671438$,_SYSSMU7_1259671438$,_SYSSMU6_1259671438$,_SYSSMU5_1259671438$,_SYSSMU4_1259671438$,_SYSSMU3_1259671438$,_SYSSMU2_1259671438$,_SYSSMU1_1259671438$)

_CORRUPTED_ROLLBACK_SEGMENTS 其中就是些需要recover的回滚段


3. 重启oracle
sql>shutdown abort
sql>startup pfile="/opt/oracle/product/11.1/dbs/initSID.ora";

4. 删除原回滚表空间
sql>drop tablespace undotbs1 including contents and datafiles;

5. 顺利删除重启oracle
sql>shutdown abort
sql>startup