今天删除一个不在使用的表空间时，碰到了ORA-22868错误。
这篇描述解决错误的过程。
删除表空间出现ORA-22868错误（一）：http://yangtingkun.itpub.net/post/468/488288
删除表空间出现ORA-22868错误（二）：http://yangtingkun.itpub.net/post/468/488304
在前面经过不断的诊断，终于搞清除了问题所在。
下面整理一下思路。首先根据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"
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
果然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。
看来是包含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表的复杂性，导致解决问题绕了这么大的一个圈子。
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2011-8-25 18:04:32 |
赞(
操作
