问题描述
今天遇到了一个奇怪的问题，有一个表空间无法删除，开始删除提示表空间内有对象，加上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，把段更新成临时段后删除成功！是什么导致这个问题将进一步查找原因,那个数据段能不能读出信息？下一步分析
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
