ITPub博客

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

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

原创 Linux操作系统 作者:yangtingkun 时间:2009-07-25 23:55:36 0 删除 编辑

今天删除一个不在使用的表空间时,碰到了ORA-22868错误。

这篇文章定位DBA_TABLES视图中查询不到表的问题。

删除表空间出现ORA-22868错误(一):http://yangtingkun.itpub.net/post/468/488288

 

 

前一篇文章描述了在删除表空间的时候碰到了ORA-22868的错误,而在诊断这个问题的过程中又碰到了其他奇怪的现象。

有一个是索引组织表,在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

首先建立一个测试的例子,手工建立一个索引组织表,也包含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 WNER = '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 WNER = '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

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

 

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

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

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10404728