今天测试手工通过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.

这样的直接修改基表的做法,在一般的情况下非常不建议使用,可能带来系统不稳定.但是在数据库异常处理过程中,可能将成为一个法宝