ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 删除表空间出现ORA-22868错误(三)

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

原创 Linux操作系统 作者:yangtingkun 时间:2009-07-26 22:31:05 0 删除 编辑

今天删除一个不在使用的表空间时,碰到了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_TABLEPURCHASEORDER的存储表,是否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_TABLELINEITEM_TABLE也罢,都是存储在USERS表空间中的,按道理DROP TABLESPACE INCLUDING CONTENTS并不应该报错。怀疑是Oraclebug

查询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表的复杂性,导致解决问题绕了这么大的一个圈子。

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-610470/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10422278