今天删除一个不在使用的表空间时，碰到了ORA-22868 错误。
这篇文章描述错误现象。
在测试CONVERT DATABASE 迁移命令时，没有迁移其中一个OFFLINE 的表空间，因为这个表空间中的内容已经无法恢复了。
迁移完成后，发现表空间和数据文件信息还保留在数据字典中，因此想要清除掉这些信息，而引发了这个错误。
SQL>; select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
EXAMPLE
YANGTK
MGMT_TABLESPACE
TEST
MGMT_ECM_DEPOT_TS
USERS
TEMP
10 rows selected.
SQL>; select name from v$datafile;
NAME
------------------------------------------------------------------------------  /data/oradata/ytktran/SYSTEM01.DBF
/
data/oradata/ytktran/UNDOTBS01.DBF
/data/oradata/ytktran/SYSAUX01.DBF
/opt/ora10g/product/10.2.0/db_1/dbs/MISSING00004
/data/oradata/ytktran/EXAMPLE01.DBF
/data/oradata/ytktran/YANGTK01.DBF
/data/oradata/ytktran/MGMT.DBF
/data/oradata/ytktran/YANGTK02.DBF
/data/oradata/ytktran/TEST01.DBF
/data/oradata/ytktran/MGMT_ECM_DEPOT1.DBF
10 rows selected.
SQL>; select file_name
2 from dba_data_files
3 where tablespace_name = 'USERS';
FILE_NAME
--------------------------------------------------------------------------------  /opt/ora10g/product/10.2.0/db_1/dbs/MISSING00004
显然USERS 表空间是要删除的表空间：
SQL>; drop tablespace users;
drop tablespace users
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option
SQL>; drop tablespace users including contents;
drop tablespace users including contents
*
ERROR at line 1:
ORA-22868: table with LOBs contains segments in different tablespaces
由于表空间不为空，因此需要INCLUDING CONTENTS 方式删除表空间，但是这时出现了ORA-22868 错误。
错误信息很明确，应该是USERS 表空间中包含了LOB 表，而LOB 表中的LOB 对象存储在USERS 表空间之外的地方。
只需要到这些对象并删除就可以解决这个问题：
SQL>; col owner format a15
SQL>; col tablespace_name format a15
SQL>; col column_name format a30
SQL>; select a.owner, a.table_name, b.column_name, b.tablespace_name
2 from dba_tables a, dba_lobs b
3 where a.owner = b.owner
4 and a.table_name = b.table_name
5 and a.tablespace_name = 'USERS'
6 and b.tablespace_name != 'USERS';
no rows selected
SQL>; select a.owner, a.table_name, b.column_name, b.tablespace_name
2 from dba_tables a, dba_lobs b
3 where a.owner = b.owner
4 and a.table_name = b.table_name
5 and a.tablespace_name = 'USERS';
no rows selected
奇怪的是，并没有符合表处于USERS 表空间中，而LOB 对象在USERS 表空间之外的LOB 对象，事实上，所有包含LOB 的表，都不在USERS 表空间中。
那么Oracle 为什么会出现上面的错误呢：
SQL>; select count(*)
2 from dba_lobs
3 where tablespace_name = 'USERS';
COUNT(*)
----------
10
SQL>; select a.owner, a.table_name, b.column_name, b.tablespace_name
2 from dba_tables a, dba_lobs b
3 where a.owner = b.owner
4 and a.table_name = b.table_name
5 and b.tablespace_name = 'USERS';drop删除表
no rows selected
SQL>; select owner, table_name, column_name, tablespace_name
2 from dba_lobs
3 where tablespace_name = 'USERS';
OWNER TABLE_NAME COLUMN_NAME TABLESPACE_NAME
----- ------------------ -------------------------------------------------- ---------------
OE LINEITEM_TABLE ";PART";.";SYS_XDBPD$"; USERS
OE LINEITEM_TABLE SYS_XDBPD$ USERS
OE ACTION_TABLE SYS_XDBPD$ USERS
OE PURCHASEORDER ";XMLDATA";.";LINEITEMS";.";SYS_XDBPD$"; USERS
OE PURCHASEORDER ";XMLDATA";.";SHIPPING_INSTRUCTIONS";.";SYS_XDBPD$"; USERS
OE PURCHASEORDER ";XMLDATA";.";REJECTION";.";SYS_XDBPD$"; USERS
OE PURCHASEORDER ";XMLDATA";.";ACTIONS";.";SYS_XDBPD$"; USERS
OE PURCHASEORDER ";XMLDATA";.";SYS_XDBPD$"; USERS
OE PURCHASEORDER ";XMLEXTRA";.";EXTRADATA"; USERS
OE PURCHASEORDER ";XMLEXTRA";.";NAMESPACES"; USERS
10 rows selected.
查询发现，USERS 表空间中包含了10 个LOB 对象。但是关联DBA_TABLES 进行查询，却发现不到任何的记录。
SQL>; SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
2 FROM DBA_OBJECTS
3 WHERE OBJECT_NAME = 'ACTION_TABLE';
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------ -------------------
OE ACTION_TABLE TABLE
SQL>; SELECT OWNER, TABLE_NAME, TABLESPACE_NAME
2 FROM DBA_TABLES
3 WHERE TABLE_NAME = 'ACTION_TABLE';
no rows selected
从DBA_OBJECTS 视图中可以看到这个对象，且对象类型为TABLE ，而在DBA_TABLES 中却不到表信息，难道在执行CONVERT DATABASE 命令过程，造成了数据字典的不一致。
查询一下DBA_TABLES 视图信息：
SQL>; SET LONG 10000
SQL>; SELECT TEXT
2 FROM DBA_VIEWS
3 WHERE VIEW_NAME = 'DBA_TABLES';
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(igflag, 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.minexts, s.maxexts,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
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, ups, 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', '?'),
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'
在DBA_TABLES 视图中没有太多的限制条件，那么导致DBA_TABLES 中没有记录的原因多半出在连接上。
检查一下OBJ$ 和TAB$ 表：
SQL>; SELECT OBJECT_ID
2 FROM DBA_OBJECTS
3 WHERE OBJECT_NAME = 'ACTION_TABLE';
OBJECT_ID
----------
52449
SQL>; SELECT OBJ#, DATAOBJ#, NAME FROM OBJ$ WHERE OBJ# = 52449;
OBJ# DATAOBJ# NAME
---------- ---------- ------------------------------
52449 ACTION_TABLE
SQL>; SELECT OBJ#, DATAOBJ#, TS#, BOBJ# FROM TAB$ WHERE OBJ# = 52449;
OBJ# DATAOBJ# TS# BOBJ#
---------- ---------- ---------- ----------
52449 0 52450

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。