ORA-01548: active rollback segment '_SYSSMU1_3138885392$' found, terminate

出现问题：

SQL> drop tablespace undotbs1 including contents and datafiles;

drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU1_3138885392$' found, terminate
dropping tablespace


1. 重新启动后查看状态，并记录NEEDS RECOVERY 的undo 块

SQL>  select SEGMENT_NAME,TABLESPACE_NAME,STATUS from dba_rollback_segs;


SEGMENT_NAME       TABLESPACE_NAME      STATUS
------------------------------ ------------------------------ ----------------
SYSTEM       SYSTEM      ONLINE
_SYSSMU10_2490256178$     UNDOTBS1   NEEDS RECOVERY
_SYSSMU9_3593450615$       UNDOTBS1   NEEDS RECOVERY
_SYSSMU8_1909280886$       UNDOTBS1   NEEDS RECOVERY
_SYSSMU7_1924883037$       UNDOTBS1    NEEDS RECOVERY
_SYSSMU6_2460248069$       UNDOTBS1    NEEDS RECOVERY
_SYSSMU5_3787622316$       UNDOTBS1    NEEDS RECOVERY
_SYSSMU4_1455318006$       UNDOTBS1    NEEDS RECOVERY
_SYSSMU3_2210742642$       UNDOTBS1     NEEDS RECOVERY
_SYSSMU2_4228238222$       UNDOTBS1     NEEDS RECOVERY
_SYSSMU1_3138885392$       UNDOTBS1     NEEDS RECOVERY
_SYSSMU20_1330014115$       UNDOTBS      ONLINE
_SYSSMU19_1127991602$       UNDOTBS      ONLINE
_SYSSMU18_779421060$         UNDOTBS      ONLINE
_SYSSMU17_1516293907$       UNDOTBS      ONLINE
_SYSSMU16_1535847501$       UNDOTBS      ONLINE
_SYSSMU15_2270880459$       UNDOTBS      ONLINE
_SYSSMU14_2794164665$       UNDOTBS      ONLINE
_SYSSMU13_3821030188$       UNDOTBS      ONLINE
_SYSSMU12_1699867927$       UNDOTBS      ONLINE
_SYSSMU11_3511819130$       UNDOTBS      ONLINE


21 rows selected.

2.修改隐藏参数

SQL> create pfile='/tmp/pfile.ora' from spfile;


File created.


SQL> shutdown immediate;
Database closed.
Database dismounted.

ORACLE instance shut down.

[oracle@oracle ~]$ vi /tmp/pfile.ora   --添加如下一行

*._CORRUPTED_ROLLBACK_SEGMENTS=(_SYSSMU10_2490256178$,_SYSSMU9_3593450615$,

_SYSSMU8_1909280886$,_SYSSMU7_1924883037$,_SYSSMU6_2460248069$,_SYSSMU5_3787622316$,

_SYSSMU4_1455318006$,_SYSSMU3_2210742642$,_SYSSMU2_4228238222$,_SYSSMU1_3138885392$)
[oracle@oracle ~]$ 
[oracle@oracle ~]$ 
[oracle@oracle ~]$ sqlplus / as sysdba


SQL*Plus: Release 11.2.0.3.0 Production on Mon Aug 3 17:53:25 2015


Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to an idle instance.


SQL> startup pfile=/tmp/pfile.ora
ORACLE instance started.


Total System Global Area  422670336 bytes
Fixed Size    1345380 bytes
Variable Size  327157916 bytes
Database Buffers   88080384 bytes
Redo Buffers    6086656 bytes
Database mounted.
Database opened.

4.删除表空间

SQL> drop tablespace undotbs1 including contents and datafiles;


Tablespace dropped.


5.重新启动表空间后查看状态

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.


Total System Global Area  422670336 bytes
Fixed Size    1345380 bytes
Variable Size  327157916 bytes
Database Buffers   88080384 bytes
Redo Buffers    6086656 bytes
Database mounted.
Database opened.
SQL> 

SQL>  select SEGMENT_NAME,TABLESPACE_NAME,STATUS from dba_rollback_segs;


SEGMENT_NAME       TABLESPACE_NAME     STATUS
------------------------------ ------------------------------ ----------------
SYSTEM       SYSTEM     ONLINE
_SYSSMU20_1330014115$       UNDOTBS     ONLINE
_SYSSMU19_1127991602$       UNDOTBS     ONLINE
_SYSSMU18_779421060$       UNDOTBS     ONLINE
_SYSSMU17_1516293907$       UNDOTBS     ONLINE
_SYSSMU16_1535847501$       UNDOTBS     ONLINE
_SYSSMU15_2270880459$       UNDOTBS     ONLINE
_SYSSMU14_2794164665$       UNDOTBS     ONLINE
_SYSSMU13_3821030188$       UNDOTBS     ONLINE
_SYSSMU12_1699867927$       UNDOTBS     ONLINE
_SYSSMU11_3511819130$       UNDOTBS     ONLINE


11 rows selected.