FILE_ID FILE_NAME TABLESPACE_NAME MB GB AUT STATUS ONLINE_
---------- -------------------------------------------------------- ------------------------------ ---------- ---------- --- --------- -------
1 /u02/oracle/JINGYU/datafile/o1_mf_system_bwp198r7_.dbf SYSTEM 700 31.9999847 YES AVAILABLE SYSTEM
2 /u02/oracle/JINGYU/datafile/o1_mf_sysaux_bwp19hl8_.dbf SYSAUX 600 31.9999847 YES AVAILABLE ONLINE
3 /u02/oracle/JINGYU/datafile/o1_mf_undotbs1_bwp19o3n_.dbf UNDOTBS1 AVAILABLE OFFLINE
5 /u02/oracle/JINGYU/datafile/o1_mf_dbs_d_ji_bwp4r7cm_.dbf DBS_D_JINGYU 100 31.9999847 YES AVAILABLE ONLINE
2.2 undo数据文件3所在的undotbs1表空间尝试删除
尝试删除直接报错ORA-01548.同时无法正常关闭数据库，无法删除活动的回滚段。具体如下：
2.2.1 创建新的undo表空间undotbs2并设置为默认的undo表空间
SQL> create undo tablespace undotbs2;
Tablespace created.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> alter system set undo_tablespace='undotbs2';
System altered.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string undotbs2
2.2.2 删除旧的undotbs1表空间失败
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
2.2.3 查看回滚段的状态，确定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
2 _SYSSMU2_3125365238$ NEEDS RECOVERY UNDOTBS1
3 _SYSSMU3_1538315859$ NEEDS RECOVERY UNDOTBS1
4 _SYSSMU4_1640924022$ NEEDS RECOVERY UNDOTBS1
5 _SYSSMU5_2892967416$ NEEDS RECOVERY UNDOTBS1
6 _SYSSMU6_3276341082$ NEEDS RECOVERY UNDOTBS1
7 _SYSSMU7_387283697$ NEEDS RECOVERY UNDOTBS1
8 _SYSSMU8_2299136685$ NEEDS RECOVERY UNDOTBS1
9 _SYSSMU9_909303715$ NEEDS RECOVERY UNDOTBS1
10 _SYSSMU10_1695440836$ NEEDS RECOVERY UNDOTBS1
10 rows selected.
2.2.4 此时正常关库会提示失败
SQL> select file#,status from v$datafile;