删除表空间出现ORA-22868错误（二）[转帖]_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛

总帖数

每页帖数

1/1页

返回列表

发起投票

查看: 3539 | 回复: 0

主题： 删除表空间出现ORA-22868错误（二）[转帖]

wayne

注册用户

等级：中校
经验：1690
发帖：221
精华：0
注册：2011-7-21
状态：离线
发送短消息息给wayne

加好友发送短消息息给wayne

发消息

发表于：

2011-8-25 18:02:33 | [全部帖] [楼主帖]

楼主

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

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

本版精华
热门帖子

操作引用/回复

总帖数

每页帖数

1/1页

返回列表

用户登录

Weblogic中间件技术论坛

Tuxedo中间件技术论坛

数据库论坛

Java论坛

Linux/unix论坛

网站地图