数据库中删除表空间出现ORA-22868错误怎么办

这篇文章主要介绍了数据库中删除表空间出现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表空间中包含了10个LOB对象。但是关联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视图中可以查询到的。

感谢你能够认真阅读完这篇文章，希望小编分享的“数据库中删除表空间出现ORA-22868错误怎么办”这篇文章对大家有帮助，同时也希望大家多多支持编程网，关注编程网精选频道，更多相关知识等着你来学习!