删除表空间出现ORA-22868异常

删除表空间出现ORA-22868错误

今天删除一个不在使用的表空间时，碰到了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';

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(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'

在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

当前对象对于的DATAOBJ# 为空，说明这个对象没有对应的存储空间，而可以看到这个对象的BOBJ# 是52450 ，查询DBA_OBJECTS 视图：

SQL> SELECT OWNER, OBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE
2 FROM DBA_OBJECTS
3 WHERE OBJECT_ID IN (52449, 52450);

OWNER OBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
--------------- ------------------------------ ---------- -------------- ------------
OE ACTION_TABLE 52449 TABLE
OE SYS_IOT_OVER_52449 52450 52450 TABLE

显然这个ACTION_TABLE 是索引组织表。查询ACTION_TABLE 对应的索引信息：

SQL> SELECT OWNER, INDEX_NAME, INDEX_TYPE
2 FROM DBA_INDEXES
3 WHERE TABLE_NAME = 'ACTION_TABLE';

OWNER INDEX_NAME INDEX_TYPE
------------------------------ ------------------------------ ---------------------------
OE ACTION_TABLE_DATA IOT - TOP
OE SYS_IL0000052449C00004$$ LOB

看来ACTION_TABLE 不仅是一个索引组织表，还包括LOB 对象。而这可能就是前面碰到的ORA-22868 错误的原因。

但是现在还有一个疑问，即使是索引组织表，也应该可以在DBA_TABLES 视图中可以查询到的。

首先建立一个测试的例子，手工建立一个索引组织表，也包含LOB 列，看看能否模拟同样的现象：

SQL> CONN YANGTK/YANGTK
Connected.
SQL> CREATE TABLE T_INDEX_ORG
2 (ID NUMBER PRIMARY KEY,
3 NAME VARCHAR2(30),
4 OTHERS CLOB)
5 ORGANIZATION INDEX
6 INCLUDING NAME OVERFLOW;

Table created.

SQL> SELECT OWNER, OBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE
2 FROM DBA_OBJECTS
3 WHERE OBJECT_NAME = 'T_INDEX_ORG'
4 AND OWNER = 'YANGTK';

OWNER OBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
--------------- ------------------------------ ---------- -------------- ----------------
YANGTK T_INDEX_ORG 95205 TABLE

SQL> SELECT INDEX_NAME, INDEX_TYPE
2 FROM USER_INDEXES
3 WHERE TABLE_NAME = 'T_INDEX_ORG';

INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
SYS_IL0000095205C00003$$ LOB
SYS_IOT_TOP_95205 IOT - TOP

SQL> SELECT OBJ#, DATAOBJ#, NAME FROM SYS.OBJ$ WHERE OBJ# = 95205;

OBJ# DATAOBJ# NAME
---------- ---------- ------------------------------
95205 T_INDEX_ORG

SQL> SELECT OBJ#, DATAOBJ#, TS#, BOBJ# FROM SYS.TAB$ WHERE OBJ# = 95205;

OBJ# DATAOBJ# TS# BOBJ#
---------- ---------- ---------- ----------
95205 0 95206

SQL> SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, OBJECT_ID, DATA_OBJECT_ID
2 FROM DBA_OBJECTS
3 WHERE OBJECT_ID = 95206;

OWNER OBJECT_NAME OBJECT_TYPE OBJECT_ID DATA_OBJECT_ID
--------------- ------------------------------ ------------------- ---------- --------------
YANGTK SYS_IOT_OVER_95205 TABLE 95206 95206

当前这个例子模仿了ACTION_TABLE ，下面看看在DBA_TABLES 中能否看到当前的T_INDEX_ORG 表：

SQL> SELECT OWNER, TABLE_NAME, TABLESPACE_NAME
2 FROM DBA_TABLES
3 WHERE TABLE_NAME = 'T_INDEX_ORG'
4 AND OWNER = 'YANGTK';

OWNER TABLE_NAME TABLESPACE_NAME
--------------- ------------------------------ ------------------------------
YANGTK T_INDEX_ORG

这说明T_INDEX_ORG 表和ACTION_TABLE 表仍然不一样。

如果不是Oracle 的数据字典存在不一致的，就是ACTION_TABLE 还有什么与众不同的地方，查询一下ACTION_TABLE 的表定义：

SQL> DESC OE.ACTION_TABLES
ERROR:
ORA-04043: object OE.ACTION_TABLES does not exist

SQL> DESC OE.ACTION_TABLE
Name Null? Type
------------------------------------------------------- -------- ------------------------
SYS_XDBPD$ XDB.XDB$RAW_LIST_T
ACTIONED_BY VARCHAR2(10 CHAR)
DATE_ACTIONED DATE

SQL> DESC XDB.XDB$RAW_LIST_T
XDB.XDB$RAW_LIST_T VARRAY(1000) OF RAW(2000)

ACTION_TABLE 的结构果然比较复杂，里面居然包含了其他的对象。那么看看ACTION_TABLE 具体的表结构：

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE', 'ACTION_TABLE', 'OE') FROM DUAL;
ERROR:
ORA-31603: object "ACTION_TABLE" of type TABLE not found in schema "OE"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA", line 2805
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1

no rows selected

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE_DATA', 'ACTION_TABLE', 'OE') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE_DATA','ACTION_TABLE','OE')
--------------------------------------------

SQL> SELECT DBMS_METADATA.GET_DDL('INDEX', 'ACTION_TABLE_DATA', 'OE') FROM DUAL;

DBMS_METADATA.GET_DDL('INDEX','ACTION_TABLE_DATA','OE')
--------------------------------------------

CREATE UNIQUE INDEX "OE"."ACTION_TABLE_DATA" ON "OE"."ACTION_TABLE" ("NESTED_TABLE_ID", "SYS_NC_ARRAY_INDEX$")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"

果然很不同：在OE 中居然找不到ACTION_TABLE 表的定义，而用TABLE_DATA 的方式得到空的结果。ACTION_TABLE 的主键可以得到DDL 定义，但是这个定义本身就很奇怪。

刚才已经确定了ACTION_TABLE 是一个索引组织表，而ACTION_TABLE_DATA 就是索引组织表的主键列。而从主键信息看，居然没有包括ACTION_TABLES 里面的任何一列。索引组织表里面的主键列居然都是系统隐藏列。如果对数据库的嵌套表和VARRAY 比较熟悉的话，就知道这两列一个嵌套表的ID 列，另一个是数组VARRAY 的索引列。

也就是说ACTION_TABLE 还是一个嵌套表，情况越来越复杂了，现在已经想象不到这个表的DDL 是如何实现的了。

既然ACTION_TABLE 是嵌套表，可以从嵌套表的相关视图中进行查询：

SQL> SELECT OWNER, TABLE_NAME, TABLE_TYPE_NAME, PARENT_TABLE_NAME
2 FROM DBA_NESTED_TABLES
3 WHERE TABLE_NAME = 'ACTION_TABLE';

OWNER TABLE_NAME TABLE_TYPE_NAME PARENT_TABLE_NAME
---------- -------------------- ------------------------- ------------------------------
OE ACTION_TABLE ACTION_V PURCHASEORDER

居然这么复杂的ACTION_TABLE 还不是主表，它还有个父表，是PURCHASEORDER 。

查询一下PURCHASEORDER 的表结构：

SQL> DESC OE.PURCHASEORDER
Name Null? Type
-------------------------------------------- -------- -------------------------
TABLE of XMLTYPE(XMLSchema "http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd" Element "PurchaseOrder") STORAGE Object-relational TYPE "PURCHASEORDER_T"

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE', 'PURCHASEORDER', 'OE') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','PURCHASEORDER','OE')
--------------------------------------------

CREATE TABLE "OE"."PURCHASEORDER" OF "SYS"."XMLTYPE"

XMLSCHEMA "http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd" ELEMENT "PurchaseOr
der" ID 3020 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
VARRAY "XMLEXTRA"."NAMESPACES" STORE AS LOB "NAMESPACES207_L"
(ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
CACHE
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
VARRAY "XMLEXTRA"."EXTRADATA" STORE AS LOB "EXTRADATA206_L"
(ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
CACHE
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
VARRAY "XMLDATA"."SYS_XDBPD$" STORE AS LOB "SYS_XDBPD$201_L"
(ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
CACHE
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
VARRAY "XMLDATA"."ACTIONS"."SYS_XDBPD$" STORE AS LOB "SYS_XDBPD$202_L"
(ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
CACHE
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
VARRAY "XMLDATA"."REJECTION"."SYS_XDBPD$" STORE AS LOB "SYS_XDBPD$203_L"
(ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
CACHE
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
VARRAY "XMLDATA"."SHIPPING_INSTRUCTIONS"."SYS_XDBPD$" STORE AS LOB "SYS_XDBPD$204_L"
(ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
CACHE
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
VARRAY "XMLDATA"."LINEITEMS"."SYS_XDBPD$" STORE AS LOB "SYS_XDBPD$205_L"
(ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
CACHE
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
VARRAY "XMLDATA"."ACTIONS"."ACTION" STORE AS TABLE "ACTION_TABLE"
(( PRIMARY KEY ("NESTED_TABLE_ID", "SYS_NC_ARRAY_INDEX$") ENABLE)
ORGANIZATION INDEX PCTTHRESHOLD 50
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" OVERFLOW
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ) RETURN AS LOCATOR
VARRAY "XMLDATA"."LINEITEMS"."LINEITEM" STORE AS TABLE "LINEITEM_TABLE"
(( PRIMARY KEY ("NESTED_TABLE_ID", "SYS_NC_ARRAY_INDEX$") ENABLE)
ORGANIZATION INDEX PCTTHRESHOLD 50
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" OVERFLOW
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ) RETURN AS LOCATOR

刚才说ACTION_TABLE 很复杂一点都没有错，不但里面的列包含了对象和数组，本身还是索引组织表、嵌套表、对象表。但是和PURCHASEORDER 表比较简直是小巫见大巫，因为ACTION_TABLE 只是PURCHASEORDER 表的一个数组属性而已。说实话PURCHASEORDER 表是目前见过的最复杂的表结构了，还涉及到了XDB 的内容。

现在查询不到ACTION_TABLE 一点也不奇怪了，一方面ACTION_TABLE 只是PURCHASEORDER 表的一部分，以嵌套表的方式保存主表数组内容。另一方面，ACTION_TABLE 是个对象表，而对象表的定义在DBA_TABLES 中是不存在的，需要查询DBA_ALL_TABLES 才能看到：

SQL> SELECT OWNER, TABLE_NAME, TABLE_TYPE, NESTED
2 FROM DBA_ALL_TABLES
3 WHERE TABLE_NAME = 'ACTION_TABLE';

OWNER TABLE_NAME TABLE_TYPE NES
---------- ------------------------------ ------------------------------ ---
OE ACTION_TABLE ACTION_T YES

到现在为止，所有的疑问终于完全解开了。

下面整理一下思路。首先根据ORA-22868 的错误信息，推断应该存在对象存储在USERS 表空间中，而LOB 对象存储在其他表空间。

但是查询没有找到这样的对象，却发现了USERS 表空间中存在一些LOB 段，查询其中一个ACTION_TABLE 表，发现在DBA_TABLES 中找不到对应的信息，于是引出了一系列探索这个表的结构的过程。

最终发现这个ACTION_TABLE 仅仅是OE 用户下的PURCHASEORDER 表中的一个嵌套表的存储表。

下面再次检查USERS 表空间的LOB 对象：

SQL> SELECT OWNER, TABLE_NAME, COLUMN_NAME
2 FROM DBA_LOBS
3 WHERE TABLESPACE_NAME = 'USERS';

OWNER TABLE_NAME COLUMN_NAME
---------- ------------------------------ --------------------------------------------------
OE LINEITEM_TABLE "PART"."SYS_XDBPD$"
OE LINEITEM_TABLE SYS_XDBPD$
OE ACTION_TABLE SYS_XDBPD$
OE PURCHASEORDER "XMLDATA"."LINEITEMS"."SYS_XDBPD$"
OE PURCHASEORDER "XMLDATA"."SHIPPING_INSTRUCTIONS"."SYS_XDBPD$"
OE PURCHASEORDER "XMLDATA"."REJECTION"."SYS_XDBPD$"
OE PURCHASEORDER "XMLDATA"."ACTIONS"."SYS_XDBPD$"
OE PURCHASEORDER "XMLDATA"."SYS_XDBPD$"
OE PURCHASEORDER "XMLEXTRA"."EXTRADATA"
OE PURCHASEORDER "XMLEXTRA"."NAMESPACES"

10 rows selected.

ACTION_TABLE 是PURCHASEORDER 的存储表，是否LINEITEM_TABLE 也是呢：

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE', 'PURCHASEORDER', 'OE') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','PURCHASEORDER','OE')
--------------------------------------------

CREATE TABLE "OE"."PURCHASEORDER" OF "SYS"."XMLTYPE"

果然LINEITEM_TABLE 也是PURCHASEORDER 的嵌套存储表。

那么手工尝试删除PURCHASEORDER 表，再次删除表空间，检查问题是否仍然存在：

SQL> DROP TABLE OE.PURCHASEORDER PURGE;

Table dropped.

SQL> DROP TABLESPACE USERS INCLUDING CONTENTS;

Tablespace dropped.

删除PURCHASEORDER 表后，表空间删除果然成功了，至此问题解决。

但是这个现象和Oracle 给出的错误信息并不相符，因为PURCHASEORDER 表也好，ACTION_TABLE 或LINEITEM_TABLE 也罢，都是存储在USERS 表空间中的，按道理DROP TABLESPACE INCLUDING CONTENTS 并不应该报错。怀疑是Oracle 的bug 。

查询METALINK ，果然发现了对应的bug 描述：Doc ID: 758602.1 删除的表空间内含有包含LOBS对象的IOT表，导致表空间无法删除。

看来是包含LOB 的索引组织表引发的问题，那么看看能否手工重现这个问题：

SQL> CREATE TABLESPACE USERS
2 DATAFILE '/data/oradata/ytktran/USERS01.DBF' SIZE 100M;

Tablespace created.

SQL> CONN YANGTK/YANGTK
Connected.
SQL> CREATE TABLE T_IOT_LOB
2 (ID NUMBER PRIMARY KEY,
3 NAME VARCHAR2(30),
4 OTHERS CLOB)
5 ORGANIZATION INDEX TABLESPACE USERS OVERFLOW TABLESPACE USERS
6 LOB (OTHERS) STORE AS OTHERS_LOB (TABLESPACE USERS);

Table created.

SQL> SELECT TABLE_NAME, TABLESPACE_NAME
2 FROM USER_TABLES
3 WHERE TABLE_NAME = 'T_IOT_LOB';

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
T_IOT_LOB

SQL> SELECT INDEX_NAME, TABLESPACE_NAME
2 FROM USER_INDEXES
3 WHERE TABLE_NAME = 'T_IOT_LOB';

INDEX_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SYS_IL0000095440C00003$$ USERS
SYS_IOT_TOP_95440 USERS

SQL> SELECT TABLE_NAME, COLUMN_NAME, TABLESPACE_NAME
2 FROM USER_LOBS
3 WHERE TABLE_NAME = 'T_IOT_LOB';

TABLE_NAME COLUMN_NAME TABLESPACE_NAME
------------------------------ ---------------------------------------- ---------------
T_IOT_LOB OTHERS USERS

SQL> DROP TABLESPACE USERS INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE USERS INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-22868: table with LOBs contains segments in different tablespaces

SQL> DROP TABLE T_IOT_LOB;

Table dropped.

SQL> DROP TABLESPACE USERS INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

问题可以重现，看来就是这个bug 导致的问题，不过由于OE.PURCHASEORDER 表的复杂性，导致解决问题绕了这么大的一个圈子。

参考至:http://hi.baidu.com/%C8%FD%B7%C9%D4%C6/blog/item/ab6192b78b4601fd30add1b5.html

http://yangtingkun.itpub.net/post/468/488318

http://yangtingkun.itpub.net/post/468/488304

http://yangtingkun.itpub.net/post/468/488288
如有错误，欢迎指正
邮箱:czmcj@163.com

删除表空间出现ORA-22868异常

相关推荐