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;

? ? FILE# STATUS
---------- -------
? ? ? ? 1 SYSTEM
? ? ? ? 2 ONLINE
? ? ? ? 3 OFFLINE
? ? ? ? 4 ONLINE
? ? ? ? 5 ONLINE

SQL> shutdown immediate;
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/u02/oracle/JINGYU/datafile/o1_mf_undotbs1_bwp19o3n_.dbf'

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

目前数据库无有效备份，需要把这些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.3 undo数据文件3所在的undotbs1表空间删除方法

2.3.1 修改pfile文件这几行内容