刪除表空間出現ORA-22868錯誤(一)

star2588發表於2018-05-23

今天刪除一個不在使用的表空間時,碰到了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表空間中包含了10LOB物件。但是關聯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檢視中可以查詢到的。

 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/31536640/viewspace-2155005/,如需轉載,請註明出處,否則將追究法律責任。

相關文章