Oracle删除表空间报ORA01548
由于undo表空间设置了自动增长，导致替换了好几个undo表空间，就想把原先的undo表空间删掉腾出空间
但删的时候报错
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_3780397527$' found, terminate
dropping tablespace
错误原因： 删除undo表空间中的segment需要回复 解决方法： 删除回滚段信息
这是因为分布式事务没有提交导致的吗，看看表空间段的情况
SQL> Select segment_id,segment_name,status,tablespace_name
from dba_rollback_segs where status not in('ONLINE','OFFLINE'); 2
SEGMENT_ID SEGMENT_NAME STATUS TABLESPACE_NAME
---------- ------------------------------ ---------------- ------------------------------
10 _SYSSMU10_3550978943$ PARTLY AVAILABLE UNDOTBS1
9 _SYSSMU9_1424341975$ PARTLY AVAILABLE UNDOTBS1
8 _SYSSMU8_2012382730$ PARTLY AVAILABLE UNDOTBS1
7 _SYSSMU7_3286610060$ PARTLY AVAILABLE UNDOTBS1
6 _SYSSMU6_2443381498$ PARTLY AVAILABLE UNDOTBS1
5 _SYSSMU5_1527469038$ PARTLY AVAILABLE UNDOTBS1
4 _SYSSMU4_1152005954$ PARTLY AVAILABLE UNDOTBS1
3 _SYSSMU3_2097677531$ PARTLY AVAILABLE UNDOTBS1
2 _SYSSMU2_2232571081$ PARTLY AVAILABLE UNDOTBS1
1 _SYSSMU1_3780397527$ PARTLY AVAILABLE UNDOTBS1
22 _SYSSMU22_888340975$ PARTLY AVAILABLE UNDOTBS2
SEGMENT_ID SEGMENT_NAME STATUS TABLESPACE_NAME
---------- ------------------------------ ---------------- ------------------------------
21 _SYSSMU21_2646097468$ PARTLY AVAILABLE UNDOTBS2
20 _SYSSMU20_1273092953$ PARTLY AVAILABLE UNDOTBS2
19 _SYSSMU19_2470562502$ PARTLY AVAILABLE UNDOTBS2
18 _SYSSMU18_420064975$ PARTLY AVAILABLE UNDOTBS2
17 _SYSSMU17_1769081034$ PARTLY AVAILABLE UNDOTBS2
16 _SYSSMU16_872709384$ PARTLY AVAILABLE UNDOTBS2
15 _SYSSMU15_1829973224$ PARTLY AVAILABLE UNDOTBS2
14 _SYSSMU14_2564326661$ PARTLY AVAILABLE UNDOTBS2
13 _SYSSMU13_307811187$ PARTLY AVAILABLE UNDOTBS2
状态为PARTLY AVAILABLE说明还有事务没有结束，看看需要恢复的段
SQL> select SEGMENT_NAME,TABLESPACE_NAME,STATUS from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
_SYSSMU12_4168347562$ UNDOTBS1 OFFLINE
_SYSSMU11_4050559276$ UNDOTBS1 OFFLINE
_SYSSMU10_3550978943$ UNDOTBS1 PARTLY AVAILABLE
_SYSSMU9_1424341975$ UNDOTBS1 PARTLY AVAILABLE
_SYSSMU8_2012382730$ UNDOTBS1 PARTLY AVAILABLE
_SYSSMU7_3286610060$ UNDOTBS1 PARTLY AVAILABLE
_SYSSMU6_2443381498$ UNDOTBS1 PARTLY AVAILABLE
_SYSSMU5_1527469038$ UNDOTBS1 PARTLY AVAILABLE
_SYSSMU4_1152005954$ UNDOTBS1 PARTLY AVAILABLE
_SYSSMU3_2097677531$ UNDOTBS1 PARTLY AVAILABLE
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
_SYSSMU2_2232571081$ UNDOTBS1 PARTLY AVAILABLE
_SYSSMU1_3780397527$ UNDOTBS1 PARTLY AVAILABLE
_SYSSMU25_243928486$ UNDOTBS2 OFFLINE
_SYSSMU24_4009307888$ UNDOTBS2 OFFLINE
_SYSSMU23_2616232510$ UNDOTBS2 OFFLINE
_SYSSMU22_888340975$ UNDOTBS2 PARTLY AVAILABLE
_SYSSMU21_2646097468$ UNDOTBS2 PARTLY AVAILABLE
_SYSSMU20_1273092953$ UNDOTBS2 PARTLY AVAILABLE
_SYSSMU19_2470562502$ UNDOTBS2 PARTLY AVAILABLE
_SYSSMU18_420064975$ UNDOTBS2 PARTLY AVAILABLE
_SYSSMU17_1769081034$ UNDOTBS2 PARTLY AVAILABLE
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
_SYSSMU16_872709384$ UNDOTBS2 PARTLY AVAILABLE
_SYSSMU15_1829973224$ UNDOTBS2 PARTLY AVAILABLE
_SYSSMU14_2564326661$ UNDOTBS2 PARTLY AVAILABLE
_SYSSMU13_307811187$ UNDOTBS2 PARTLY AVAILABLE
_SYSSMU35_65240735$ UNDOTBS3 ONLINE
_SYSSMU34_3144578968$ UNDOTBS3 ONLINE
_SYSSMU33_3204464002$ UNDOTBS3 ONLINE
_SYSSMU32_3560741359$ UNDOTBS3 ONLINE
_SYSSMU31_3558146288$ UNDOTBS3 ONLINE
_SYSSMU30_2956244594$ UNDOTBS3 ONLINE
_SYSSMU29_2102016512$ UNDOTBS3 ONLINE
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
_SYSSMU28_1084061334$ UNDOTBS3 ONLINE
_SYSSMU27_2646077545$ UNDOTBS3 ONLINE
_SYSSMU26_2780995135$ UNDOTBS3 ONLINE
发现undotbs1，2中的回滚段status为PARTLY AVAILABLE /NEED RECOVERY这时如果有rman备份可以利用rman恢复
没有的通过设置参数文件值解决：
SQL> create pfile from spfile;
添加该参数，并将需要恢复的段名放进去
_corrupted_rollback_segments=(_SYSSMU10_3550978943$,
_SYSSMU9_1424341975$,
_SYSSMU8_2012382730$,
_SYSSMU7_3286610060$,
_SYSSMU6_2443381498$,
_SYSSMU5_1527469038$,
_SYSSMU4_1152005954$,
_SYSSMU3_2097677531$,
_SYSSMU2_2232571081$,
_SYSSMU1_3780397527$,
_SYSSMU22_888340975$,
_SYSSMU21_2646097468$,
_SYSSMU20_1273092953$,
_SYSSMU19_2470562502$,
_SYSSMU18_420064975$,
_SYSSMU17_1769081034$,
_SYSSMU16_872709384$,
_SYSSMU15_1829973224$,
_SYSSMU14_2564326661$,
_SYSSMU13_307811187$)
SQL> create spfile from pfile;
重启数据库，并重新删掉表空间
SQL> startup
ORACLE instance started.
Total System Global Area 6680915968 bytes
Fixed Size 2213936 bytes
Variable Size 5234493392 bytes
Database Buffers 1409286144 bytes
Redo Buffers 34922496 bytes
Database mounted.
Database opened.
SQL> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
SQL> drop tablespace undotbs2 including contents and datafiles;
Tablespace dropped.
SQL>
