數(shù)據(jù)庫(kù)中刪除表空間出現(xiàn)ORA-22868錯(cuò)誤怎么辦

這篇文章主要介紹了數(shù)據(jù)庫(kù)中刪除表空間出現(xiàn)ORA-22868錯(cuò)誤怎么辦，具有一定借鑒價(jià)值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

在測(cè)試CONVERT DATABASE遷移命令時(shí)，沒(méi)有遷移其中一個(gè)OFFLINE的表空間，因?yàn)檫@個(gè)表空間中的內(nèi)容已經(jīng)無(wú)法恢復(fù)了。

遷移完成后，發(fā)現(xiàn)表空間和數(shù)據(jù)文件信息還保留在數(shù)據(jù)字典中，因此想要清除掉這些信息，而引發(fā)了這個(gè)錯(cuò)誤。

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表空間是要?jiǎng)h除的表空間：

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方式刪除表空間，但是這時(shí)出現(xiàn)了ORA-22868錯(cuò)誤。

錯(cuò)誤信息很明確，應(yīng)該是USERS表空間中包含了LOB表，而LOB表中的LOB對(duì)象存儲(chǔ)在USERS表空間之外的地方。

只需要找到這些對(duì)象并刪除就可以解決這個(gè)問(wèn)題：

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

奇怪的是，并沒(méi)有符合表處于USERS表空間中，而LOB對(duì)象在USERS表空間之外的LOB對(duì)象，事實(shí)上，所有包含LOB的表，都不在USERS表空間中。

那么Oracle為什么會(huì)出現(xiàn)上面的錯(cuò)誤呢：

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.

查詢(xún)發(fā)現(xiàn)，USERS表空間中包含了10個(gè)LOB對(duì)象。但是關(guān)聯(lián)DBA_TABLES進(jìn)行查詢(xún)，卻發(fā)現(xiàn)找不到任何的記錄。

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視圖中可以看到這個(gè)對(duì)象，且對(duì)象類(lèi)型為TABLE，而在DBA_TABLES中卻找不到表信息，難道在執(zhí)行CONVERT DATABASE命令過(guò)程，造成了數(shù)據(jù)字典的不一致。

查詢(xún)一下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視圖中沒(méi)有太多的限制條件，那么導(dǎo)致DBA_TABLES中沒(méi)有記錄的原因多半出在連接上。

檢查一下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

當(dāng)前對(duì)象對(duì)于的DATAOBJ#為空，說(shuō)明這個(gè)對(duì)象沒(méi)有對(duì)應(yīng)的存儲(chǔ)空間，而可以看到這個(gè)對(duì)象的BOBJ#是52450，查詢(xún)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

顯然這個(gè)ACTION_TABLE是索引組織表。查詢(xún)ACTION_TABLE對(duì)應(yīng)的索引信息：

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

看來(lái)ACTION_TABLE不僅是一個(gè)索引組織表，還包括LOB對(duì)象。而這可能就是前面碰到的ORA-22868錯(cuò)誤的原因。

但是現(xiàn)在還有一個(gè)疑問(wèn)，即使是索引組織表，也應(yīng)該可以在DBA_TABLES視圖中可以查詢(xún)到的。

感謝你能夠認(rèn)真閱讀完這篇文章，希望小編分享的“數(shù)據(jù)庫(kù)中刪除表空間出現(xiàn)ORA-22868錯(cuò)誤怎么辦”這篇文章對(duì)大家有幫助，同時(shí)也希望大家多多支持億速云，關(guān)注億速云行業(yè)資訊頻道，更多相關(guān)知識(shí)等著你來(lái)學(xué)習(xí)!