联系:手机/微信(+86 17813235971) QQ(107644445)
标题:因非常规操作导致删除表空间提示ORA-01561解决办法
作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
今天测试手工通过bbed修改undo$中回滚段状态(从status$=5修改为1)[NEEDS RECOVERY 修改为 DELETE],然后尝试删除表空间,发现不能删除
删除表空间提示ORA-01561
SQL> drop tablespace undotbs; drop tablespace undotbs * ERROR at line 1: ORA-01561: failed to remove all objects in the tablespace specified SQL> drop tablespace undotbs including contents; drop tablespace undotbs including contents * ERROR at line 1: ORA-01561: failed to remove all objects in the tablespace specified
查询相关信息
SQL> select ts#,name from v$tablespace; TS# NAME ---------- ------------------------------ 0 SYSTEM 1 SYSAUX 4 USERS 6 UNDOTBS 3 TEMP SQL> select name,ts#,status$ from undo$; NAME TS# STATUS$ ------------------------------ ---------- ---------- SYSTEM 0 3 _SYSSMU1_3138885392$ 2 1 _SYSSMU2_4228238222$ 2 1 _SYSSMU3_2210742642$ 2 1 _SYSSMU4_1455318006$ 2 1 _SYSSMU5_3787622316$ 2 1 _SYSSMU6_2460248069$ 2 1 _SYSSMU7_1924883037$ 2 1 _SYSSMU8_1909280886$ 2 1 _SYSSMU9_3593450615$ 2 1 _SYSSMU10_2490256178$ 2 1 NAME TS# STATUS$ ------------------------------ ---------- ---------- _SYSSMU11_253524401$ 6 1 _SYSSMU12_842775869$ 6 1 _SYSSMU13_2794767139$ 6 1 _SYSSMU14_2067649841$ 6 1 _SYSSMU15_3270221471$ 6 1 _SYSSMU16_4094338609$ 6 1 _SYSSMU17_709661646$ 6 1 _SYSSMU18_699588262$ 6 1 _SYSSMU19_718640828$ 6 1 _SYSSMU20_3516920665$ 6 1 _SYSSMU21_793796797$ 6 1 NAME TS# STATUS$ ------------------------------ ---------- ---------- _SYSSMU22_3988785920$ 6 1 _SYSSMU23_1828333848$ 6 1 _SYSSMU24_1223218862$ 6 1 _SYSSMU25_2939844199$ 6 1 _SYSSMU26_1317300205$ 6 1 _SYSSMU27_1654033223$ 6 1 _SYSSMU28_3748619502$ 6 1 _SYSSMU29_1868765904$ 6 1 _SYSSMU30_3379578723$ 6 1 31 rows selected. SQL> select segment_name,status from dba_rollback_segs; SEGMENT_NAME STATUS ------------------------------ ---------------- SYSTEM ONLINE
通过这里可以看出,通过bbed的修改,除system回滚段外,其他均已经被标志为delete状态,对于这样的情况,很本能的怀疑是extent或者segment未被清理掉导致
查询EXTENT和SEGMENT
SQL> select SEGMENT_NAME from dba_extents where TABLESPACE_NAME='UNDOTBS'; no rows selected SQL> select segment_name from dba_segments where TABLESPACE_NAME='UNDOTBS'; no rows selected SQL> select count(*) from seg$ where ts#=6; COUNT(*) ---------- 10 SQL> select count(*) from seg$ where ts#=2; COUNT(*) ---------- 0 SQL> select file#,type# from seg$ where ts#=6; FILE# TYPE# ---------- ---------- 3 10 3 10 3 10 3 10 3 10 3 10 3 10 3 10 3 10 3 10 10 rows selected.
通过查询我们发现SEG$中含有10条记录,而通过dbms_metadata.get_ddl分析DBA_SEGMENTS是的,得出type为10恰好是TYPE2 UNDO信息.
解决办法
删除掉这些因为手工修改undo$信息导致遗留下来的后遗症对象
SQL> delete from seg$ where ts#=6; 10 rows deleted. SQL> commit; Commit complete. SQL> drop tablespace undotbs ; Tablespace dropped.
这样的直接修改基表的做法,在一般的情况下非常不建议使用,可能带来系统不稳定.但是在数据库异常处理过程中,可能将成为一个法宝
在有些案例中,看到有朋友把seg$.TYPE#修改为3(3表示临时对象),然后再删除该表空间