drop tablespace ORA-01561案例

今天遇到了一个奇怪的问题，有一个表空间无法删除，开始删除提示表空间内有对象，加上INCLUDING CONTENTS选项级连删除又报
ORA-01561: failed to remove all objects in the tablespace specified，查一些系统视图该对象还不存在

下面看来整个过程

SQL> SELECT * FROM V$VERSION WHERE ROWNUM=1;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi

SQL> select * from v$tablespace;

TS# NAME                           INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
0 SYSTEM                         YES NO  YES
1 UNDOTBS1                       YES NO  YES
2 SYSAUX                         YES NO  YES
3 TEMPTS1                        NO  NO  YES
4 ICME                           YES NO  YES
14 NCME                           YES NO  YES
7 USERS                          YES NO  YES
15 CME                            YES NO  YES
22 SANJI                          YES NO  YES
23 BJHR_DEV                       YES NO  YES

10 rows selected.

SQL> drop tablespace ncme;
drop tablespace ncme
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option

---看到提示说包含对象，但记得表空间上已没表了，下面查询一下是什么对象

SQL> select SEGMENT_NAME,SEGMENT_TYPE,OWNER from dba_segments where tablespace_NAME='NCME';

SEGMENT_NAME         SEGMENT_TYPE       OWNER
-------------------- ------------------ ------------------------------
HR_ATTENDANCE        TABLE              SYS
HR_USER              TABLE              SYS


SQL> show user
USER is "SYS"

SQL> select * from hr_user;
select * from hr_user
*
ERROR at line 1:
ORA-00942: table or view does not exist

--无法查看，试图得到建表语句也失败

SQL> select dbms_metadata.get_ddl('TABLE','HR_USER') FROM DUAL;
ERROR:
ORA-31603: object "HR_USER" of type TABLE not found in schema "SYS"
ORA-06512: at "SYS.DBMS_METADATA", line 1546
ORA-06512: at "SYS.DBMS_METADATA", line 1583
ORA-06512: at "SYS.DBMS_METADATA", line 1901
ORA-06512: at "SYS.DBMS_METADATA", line 2792
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1

--提示是表，但在在表、嵌套表、LOB查了都没有对像，且从dba_tables显示ncme表空间也没有任何表

SQL> select * from dba_tables where TABLE_NAME like 'HR_%';
no rows selected

SQL> select * from dba_tables where tablespace_name='NCME';
no rows selected

SQL> SELECT * FROM  DBA_LOBS WHERE TABLESPACE_NAME='NCME';

no rows selected

SQL> SELECT * FROM DBA_OBJECTS WHERE OBJECT_NAME='HR_USER';
no rows selected

SQL> select * from DBA_NESTED_TABLESwhere TABLE_NAME like 'HR_%';
no rows selected

--那先不考虑这个对像，删除表空间试试，结果还是失败

SQL> drop tablespace ncme including contents;
drop tablespace ncme including contents
*
ERROR at line 1:
ORA-01561: failed to remove all objects in the tablespace specified

--没有什么有效帮助，也只能说表空间里还有对象，查询alert日志也无错误

sql> !oerr ora 1561
01561, 00000, "failed to remove all objects in the tablespace specified"
// *Cause: Failed to remove all objects when dropping a tablespace
// *Action: Retry the drop tablespace until all objects are dropped



--查询一下dba_tables定义

SQL> select text from dba_viewS where view_name='DBA_TABLES';

TEXT
--------------------------------------------------------------------------------
select u.name, o.name, decode(bitand(t.property,2151678048), 0, ts.name, null),

SQL> SET long 10000000
SQL> /

TEXT
--------------------------------------------------------------------------------
select u.name, o.name, decode(bitand(t.property,2151678048), 0, ts.name, null),
decode(bitand(t.property, 1024), 0, null, co.name),
decode((bitand(t.property, 512)+bitand(t.flags, 536870912)),
0, null, co.name),
decode(bitand(t.trigflag, 1073741824), 1073741824, 'UNUSABLE', 'VALID'),
decode(bitand(t.property, 32+64), 0, mod(t.pctfree$, 100), 64, 0, null),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(t.property, 32+64), 0, t.pctused$, 64, 0, null)),
decode(bitand(t.property, 32), 0, t.initrans, null),
decode(bitand(t.property, 32), 0, t.maxtrans, null),
s.iniexts * ts.blocksize,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
s.extsize * ts.blocksize),
s.minexts, s.maxexts,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
s.extpct),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(o.flags, 2), 2, 1, decode(s.lists, 0, 1, s.lists))),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(o.flags, 2), 2, 1, decode(s.groups, 0, 1, s.groups))),
decode(bitand(t.property, 32+64), 0,
decode(bitand(t.flags, 32), 0, 'YES', 'NO'), null),
decode(bitand(t.flags,1), 0, 'Y', 1, 'N', '?'),
t.rowcnt,
decode(bitand(t.property, 64), 0, t.blkcnt, null),
decode(bitand(t.property, 64), 0, t.empcnt, null),
t.avgspc, t.chncnt, t.avgrln, t.avgspc_flb,
decode(bitand(t.property, 64), 0, t.flbcnt, null),
lpad(decode(t.degree, 32767, 'DEFAULT', nvl(t.degree,1)),10),
lpad(decode(t.instances, 32767, 'DEFAULT', nvl(t.instances,1)),10),
lpad(decode(bitand(t.flags, 8), 8, 'Y', 'N'),5),
decode(bitand(t.flags, 6), 0, 'ENABLED', 'DISABLED'),
t.samplesize, t.analyzetime,
decode(bitand(t.property, 32), 32, 'YES', 'NO'),
decode(bitand(t.property, 64), 64, 'IOT',
decode(bitand(t.property, 512), 512, 'IOT_OVERFLOW',
decode(bitand(t.flags, 536870912), 536870912, 'IOT_MAPPING', null
))),
decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N'),
decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', 'N'),
decode(bitand(t.property, 8192), 8192, 'YES',
decode(bitand(t.property, 1), 0, 'NO', 'YES')),
decode(bitand(o.flags, 2), 2, 'DEFAULT',
decode(s.cachehint, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL)),
decode(bitand(t.flags, 131072), 131072, 'ENABLED', 'DISABLED'),
decode(bitand(t.flags, 512), 0, 'NO', 'YES'),
decode(bitand(t.flags, 256), 0, 'NO', 'YES'),
decode(bitand(o.flags, 2), 0, NULL,
decode(bitand(t.property, 8388608), 8388608,
'SYS$SESSION', 'SYS$TRANSACTION')),
decode(bitand(t.flags, 1024), 1024, 'ENABLED', 'DISABLED'),
decode(bitand(o.flags, 2), 2, 'NO',
decode(bitand(t.property, 2147483648), 2147483648, 'NO',
decode(ksppcv.ksppstvl, 'TRUE', 'YES', 'NO'))),
decode(bitand(t.property, 1024), 0, null, cu.name),
decode(bitand(t.flags, 8388608), 8388608, 'ENABLED', 'DISABLED'),
decode(bitand(t.property, 32), 32, null,
decode(bitand(s.spare1, 2048), 2048, 'ENABLED', 'DISABLED')),
decode(bitand(o.flags, 128), 128, 'YES', 'NO')
from sys.user$ u, sys.ts$ ts, sys.seg$ s, sys.obj$ co, sys.tab$ t, sys.obj$ o,
sys.obj$ cx, sys.user$ cu, x$ksppcv ksppcv, x$ksppi ksppi
where o.owner# = u.user#
and o.obj# = t.obj#
and bitand(t.property, 1) = 0
and bitand(o.flags, 128) = 0
and t.bobj# = co.obj# (+)
and t.ts# = ts.ts#
and t.file# = s.file# (+)
and t.block# = s.block# (+)
and t.ts# = s.ts# (+)
and t.dataobj# = cx.obj# (+)
and cx.owner# = cu.user# (+)
and ksppi.indx = ksppcv.indx
and ksppi.ksppinm = '_dml_monitoring_enabled'


SQL> select text from dba_viewS where view_name='DBA_SEGMENTS';

TEXT
--------------------------------------------------------------------------------
select owner, segment_name, partition_name, segment_type, tablespace_name,
header_file, header_block,
decode(bitand(segment_flags, 131072), 131072, blocks,
(decode(bitand(segment_flags,1),1,
dbms_space_admin.segment_number_blocks(tablespace_id, relative_fno,
header_block, segment_type_id, buffer_pool_id, segment_flags,
segment_objd, blocks), blocks)))*blocksize,
decode(bitand(segment_flags, 131072), 131072, blocks,
(decode(bitand(segment_flags,1),1,
dbms_space_admin.segment_number_blocks(tablespace_id, relative_fno,
header_block, segment_type_id, buffer_pool_id, segment_flags,
segment_objd, blocks), blocks))),
decode(bitand(segment_flags, 131072), 131072, extents,
(decode(bitand(segment_flags,1),1,
dbms_space_admin.segment_number_extents(tablespace_id, relative_fno,
header_block, segment_type_id, buffer_pool_id, segment_flags,
segment_objd, extents) , extents))),
initial_extent, next_extent, min_extents, max_extents, pct_increase,
freelists, freelist_groups, relative_fno,
decode(buffer_pool_id, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL)
from sys_dba_segs


SQL> select text from dba_viewS where view_name='SYS_DBA_SEGS';

TEXT
--------------------------------------------------------------------------------
select NVL(u.name, 'SYS'), o.name, o.subname,
so.object_type, s.type#,
ts.ts#, ts.name, ts.blocksize,
f.file#, s.block#,
s.blocks * ts.blocksize, s.blocks, s.extents,
s.iniexts * ts.blocksize,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
s.extsize * ts.blocksize),
s.minexts, s.maxexts,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
s.extpct),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(s.lists, 0, 1, s.lists)),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(s.groups, 0, 1, s.groups)),
s.file#, s.cachehint, NVL(s.spare1,0), o.dataobj#
from sys.user$ u, sys.obj$ o, sys.ts$ ts, sys.sys_objects so, sys.seg$ s,
sys.file$ f
where s.file# = so.header_file
and s.block# = so.header_block
and s.ts# = so.ts_number
and s.ts# = ts.ts#
and o.obj# = so.object_id
and o.owner# = u.user# (+)
and s.type# = so.segment_type_id
and o.type# = so.object_type_id
and s.ts# = f.ts#
and s.file# = f.relfile#
union all
select NVL(u.name, 'SYS'), un.name, NULL,
decode(s.type#, 1, 'ROLLBACK', 10, 'TYPE2 UNDO'), s.type#,
ts.ts#, ts.name, ts.blocksize, f.file#, s.block#,
s.blocks * ts.blocksize, s.blocks, s.extents,
s.iniexts * ts.blocksize, s.extsize * ts.blocksize, s.minexts,
s.maxexts, s.extpct,
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(s.lists, 0, 1, s.lists)),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(s.groups, 0, 1, s.groups)),
s.file#, s.cachehint, NVL(s.spare1,0), un.us#
from sys.user$ u, sys.ts$ ts, sys.undo$ un, sys.seg$ s, sys.file$ f
where s.file# = un.file#
and s.block# = un.block#
and s.ts# = un.ts#
and s.ts# = ts.ts#
and s.user# = u.user# (+)
and s.type# in (1, 10)
and un.status$ != 1
and un.ts# = f.ts#
and un.file# = f.relfile#
union all
select NVL(u.name, 'SYS'), to_char(f.file#) || '.' || to_char(s.block#), NULL,
decode(s.type#, 2, 'DEFERRED ROLLBACK', 3, 'TEMPORARY',
4, 'CACHE', 9, 'SPACE HEADER', 'UNDEFINED'), s.type#,
ts.ts#, ts.name, ts.blocksize,
f.file#, s.block#,
s.blocks * ts.blocksize, s.blocks, s.extents,
s.iniexts * ts.blocksize,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
s.extsize * ts.blocksize),
s.minexts, s.maxexts,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
s.extpct),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(s.lists, 0, 1, s.lists)),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(s.groups, 0, 1, s.groups)),
s.file#, s.cachehint, NVL(s.spare1,0), s.hwmincr
from sys.user$ u, sys.ts$ ts, sys.seg$ s, sys.file$ f
where s.ts# = ts.ts#
and s.user# = u.user# (+)
and s.type# not in (1, 5, 6, 8, 10)
and s.ts# = f.ts#
and s.file# = f.relfile#

--发现dba_tables关连很多，可能在表关连时被筛下去了？很有可能，直接查基表

SQL> select OBJ#,DATAOBJ#,OWNER#,SUBNAME,TYPE#,CTIME from obj$ where name='HR_USER';

OBJ#   DATAOBJ#     OWNER# SUBNAME                             TYPE# CTIME
---------- ---------- ---------- ------------------------------ ---------- -------------------
30907      30907         85                                         2 2010-01-20 10:53:16

type# 只说几种常见类型
1 = INDEX, 2 = TABLE, 3 = CLUSTER, 4 = VIEW, 5 = SYNONYM, 6 = SEQUENCE,
7 = PROCEDURE, 8 = FUNCTION, 9 = PACKAGE, 10 = NON-EXISTENT,11 = PACKAGE BODY, 12 = TRIGGER...


SQL> SELECT * FROM DBA_OBJECTS WHERE OBJECT_ID=30907;

no rows selected

SQL> SELECT * FROM DBA_OBJECTS WHERE data_object_id=30907;

no rows selected

SQL>SELECT USER#,NAME FROM USER$ ORDER BY 1

USER# NAME
---------- ------------------------------
0 SYS
1 PUBLIC
2 CONNECT
3 RESOURCE
4 DBA
5 SYSTEM
6 SELECT_CATALOG_ROLE
7 EXECUTE_CATALOG_ROLE
8 DELETE_CATALOG_ROLE
9 EXP_FULL_DATABASE
10 IMP_FULL_DATABASE
11 OUTLN
12 RECOVERY_CATALOG_OWNER
13 GATHER_SYSTEM_STATISTICS
14 LOGSTDBY_ADMINISTRATOR
15 AQ_ADMINISTRATOR_ROLE
16 AQ_USER_ROLE
17 GLOBAL_AQ_USER_ROLE
18 SCHEDULER_ADMIN
19 DIP
20 HS_ADMIN_ROLE
21 TSMSYS
22 OEM_ADVISOR
23 OEM_MONITOR
24 DBSNMP
29 OPER
30 CME
40 S_NQ_SCHED
42 SSE_ROLE
69 PERFSTAT
91 EXAM_ALL
92 OLEM
103 SQPX
114 PLUSTRACE
115 HYSH
120 ICME

--到这已发现那个对象所属的owner 非sys,而真正的user# 85已不存在了，看视图的创建脚本select NVL(u.name, 'SYS')

SQL> select * from v$tablespace;

TS# NAME                           INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
0 SYSTEM                         YES NO  YES
1 UNDOTBS1                       YES NO  YES
2 SYSAUX                         YES NO  YES
3 TEMPTS1                        NO  NO  YES
4 ICME                           YES NO  YES
14 NCME                           YES NO  YES
7 USERS                          YES NO  YES
15 CME                            YES NO  YES
22 SANJI                          YES NO  YES
23 BJHR_DEV                       YES NO  YES

10 rows selected.

SQL> select * from seg$ where ts#=14;

FILE#     BLOCK#      TYPE#        TS#     BLOCKS    EXTENTS    INIEXTS    MINEXTS    MAXEXTS    EXTSIZE     EXTPCT      USER#      LISTS     GROUPS

BITMAPRANGES  CACHEHINT
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --

---------- ----------
SCANHINT    HWMINCR     SPARE1     SPARE2
---------- ---------- ---------- ----------
8         11          5         14          8          1          8          1 2147483645        128          0         85          0          0

0          0
0      30906     131329

8         19          5         14          8          1          8          1 2147483645        128          0         85          0          0

0          0
0      30907     131329

--表空间中的确是有两个数据对象，确认是无效seg后更新成临时段，表空间即可删除(在这多谢网友“简单”提示)

SQL> update seg$ set type#=3 where ts#=14;

2 rows updated.

SQL> commit;
Commit complete.
SQL> drop tablespace ncme including contents;

Tablespace dropped.

SQL> alter system flush shared_pool;

System altered.

seg$ 表type字段含意
1 = UNDO, 2 = SAVE UNDO, 3 = TEMPORARY, 4 = CACHE, 5 = DATA, 6 = INDEX
7 = SORT  8 = LOB   9 = Space Header 10 = System Managed Undo
复制

到这里删除表空间问题解决，是因为空间里的数据对象没有owner，把段更新成临时段后删除成功！是什么导致这个问题将进一步查找原因,那个数据段能不能读出信息？下一步分析