ORA-01548: 已找到活动回退段‘_SYSSMU1$‘，终止删除表空间 的解决办法

        最近在操作数据库删除还原表空间时遇到此问题，通过查询网上方法没的有一次性很好解决，又或是操作步骤不够明白，无从下手，因此将自己解决的步骤记录下来，方便后续查看。

1 删除损坏数据文件所在表空间

1.1 故障现象

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_1401565358$' found, terminate dropping tablespace

1.2 查看回滚段的状态，确定undotbs1表空间的回滚段状态都是NEEDS RECOVERY

SQL> select segment_id, segment_name,status,tablespace_name from dba_rollback_segs where status not in ('ONLINE','OFFLINE');
SEGMENT_ID SEGMENT_NAME                   STATUS           TABLESPACE_NAME
---------- ------------------------------ ---------------- ------------------------------
         1 _SYSSMU1_1401565358$           NEEDS RECOVERY   UNDOTBS1
1 rows selected.

1.3 此时删除回滚段也会提示失败

目前数据库无有效备份，需要把这些NEEDS RECOVERY的undo rollback segs删除

SQL> drop  rollback segment "_SYSSMU10_1695440836$";
drop  rollback segment "_SYSSMU10_1695440836$"
*
ERROR at line 1:
ORA-30025: DROP segment '_SYSSMU10_1695440836$' (in undo tablespace) not allowed

2 undo数据文件所在的undotbs1表空间删除方法

2.1 修改pfile文件这几行内容

将1.2中查询出来的需要恢复的segment_name插入，其中_offline_rollback_segments参数中的回滚段的名字在上面查到过。

*.undo_management='MANUAL'
*._offline_rollback_segments=(_SYSSMU1_1401565358$,_SYSSMU2_3125365238$,_SYSSMU3_1538315859$,_SYSSMU4_1640924022$,_SYSSMU5_2892967416$,_SYSSMU6_3276341082$,_SYSSMU7_387283697$,_SYSSMU8_2299136685$,_SYSSMU9_909303715$,_SYSSMU10_1695440836$)
#*.undo_tablespace='undotbs1'

2.2 使用sysdba账户登录数据库

打开cmd命令窗口，输入sqlplus / as sysdba

SQL> C:\Users\Administrator>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on 星期五 10月 15 10:01:41 2021
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

2.3 先关闭数据库

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

2.4 使用pfile文件启动数据库

SQL> startup pfile='D:\app\Administrator\admin\orcl\pfile\init.ora.1212019135331';
ORACLE instance started.
Total System Global Area 1620115456 bytes
Fixed Size                  2253704 bytes
Variable Size             989858936 bytes
Database Buffers          620756992 bytes
Redo Buffers                7245824 bytes
Database mounted.
Database opened.

2.5 删除回滚段成功

SQL>  drop rollback segment "_SYSSMU10_1695440836$";
Rollback segment dropped.

2.6 删除回滚表空间undotbs1成功

SQL> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.

2.7 此时查询数据库的相关信息

SQL>  select segment_id, segment_name,status,tablespace_name from dba_rollback_segs where status not in ('ONLINE','OFFLINE')
  2  ;
no rows selected
SQL> select FILE_ID, FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024 "MB", MAXBYTES/1024/1024/1024 "GB", AUTOEXTENSIBLE, STATUS, ONLINE_STATUS from dba_data_files;
   FILE_ID FILE_NAME                                                TABLESPACE_NAME                        MB         GB AUT STATUS    ONLINE_
---------- -------------------------------------------------------- ------------------------------ ---------- ---------- --- --------- -------
         1 /u02/oracle/HUNDSUN/datafile/o1_mf_system_bwp198r7_.dbf   SYSTEM                                700 31.9999847 YES AVAILABLE SYSTEM
         2 /u02/oracle/HUNDSUN/datafile/o1_mf_sysaux_bwp19hl8_.dbf   SYSAUX                                600 31.9999847 YES AVAILABLE ONLINE
         4 /u02/oracle/HUNDSUN/datafile/o1_mf_undotbs2_bwqxbnxo_.dbf UNDOTBS2                              100 31.9999847 YES AVAILABLE ONLINE
         5 /u02/oracle/HUNDSUN/datafile/o1_mf_dbs_d_ji_bwp4r7cm_.dbf DBS_D_HUNDSUN     

检查是否成功删除还原表空间文件，发现此时一切数据文件正常,此时已经可以正常关库

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

2.8 以spfile正常启动数据库，检查一切正常

SQL> startup
ORACLE instance started.
Total System Global Area 1620115456 bytes
Fixed Size                  2253704 bytes
Variable Size             989858936 bytes
Database Buffers          620756992 bytes
Redo Buffers                7245824 bytes
Database mounted.
Database opened.