ORA-8103 "object no longer exists"错误

一：版本信息

操作系统版本：AIX 61009

数据库版本：11.2.0.3.11(RAC)

二：错误描述

  今天研发过来说他们的过程报ORA-08103错误，我查了一下他们过程中用到的表，都是正常的！

三：错误原因及解决方案

  OERR: ORA-8103 "object no longer exists" Master Note / Troubleshooting, Diagnostic and Solution (Doc ID 8103.1)上的这篇文档给我们清楚的列出了可能导致该报错的原因(该报错有多种可能，我只简单的讲一下我目前碰到的情况)

  mos中有如下这段表述:

Description

ORA-8103 is reporting that a SQL statement found a block that no longer belongs to the object referenced in the statement. 

Cause 

ORA-8103 is caused by an invalid block type. The block header has an invalid block type or the block type inside the block is not expected; e.g. a data block (Type=6) was expected but the actual block information is not a data block (Type!=6).  >>>ORA-8103可能是由于无效的块类型导致,例如一个块被认为是数据块，但是实际上根据块里记录的信息发现它不是数据块(每个块都会有type标识)

ORA-8103 is also caused by an unexpected data_object_id where it is changing for the involved objects while the affected SQL statement is executed.   >>>ORA-8103也可能是由于涉及到相关对象的sql已经执行，但是在sql操作完成之前，对象的data_object_id 发生了变化导致的！(data_object_id 可能会因为如下操作发生变化：truncate table;alter index ... rebuild;alter table move;)>>>>注意如果truncate一张空表，并不会导致表的data_object_id发生变化

These two causes might be due to an expected behavior or other problems.  Details are:

Expected behavior.

Tables are being dropped/truncated while a SQL statement for those tables is still in execution. In the case of an index, it might be caused by an index rebuild. In other words the object has been deleted by another session since the operation began.

Look if dba_objects.data_object_id is changing for the affected object while queries are being executed.

data_object_id is changed by DDL statements like:

truncate table
alter index .. rebuild
alter table .. move
etc.

   文档看到这，我已经找到了我们系统中产生报错的原因，我们一直在压缩一些历史的分区表，研发报错的时候我们也的压缩脚本正在执行。

alter table xxx.xxxx move partition xxxx compress;

  下面我们来做一个小实验，验证一下因为表的data_object_id 改变导致访问该表报ORA-8103：

1)检查表的data_block_id

SQL> select owner,object_name,object_id,data_object_id from dba_objects where object_name='DIM_SERVICE_UA_150107HIS';

OWNER                          OBJECT_NAME                     OBJECT_ID DATA_OBJECT_ID
------------------------------ ------------------------------ ---------- --------------
CHANNEL                        DIM_SERVICE_UA_150107HIS           239515         246160

2)move 表

alter table DIM_SERVICE_UA_150107HIS move;

##注意在move动作还未完成之前，表的data_object_id不会发生变化

3) 访问表
select count(10) from DIM_SERVICE_UA_150107HIS;

##sql正常运行等待结果

4)第一步中执行的 move动作完成

4)第三步的访问报错

select count(10) from DIM_SERVICE_UA_150107HIS
                      *
ERROR at line 1:
ORA-08103: object no longer exists

##表的move动作完成后，select操作紧接着就报了 ORA-08103错误。(因为此时data_object_id已经发生变化)

5)再次查看表的data_object_id

SQL> select owner,object_name,object_id,data_object_id from dba_objects where object_name='DIM_SERVICE_UA_150107HIS';

OWNER                          OBJECT_NAME                     OBJECT_ID DATA_OBJECT_ID
------------------------------ ------------------------------ ---------- --------------
CHANNEL                        DIM_SERVICE_UA_150107HIS           239515         246161

##要模拟出该报错，实验中有几个地方要注意

第一：我们需要在 alter move命令完成之前执行表的访问操作(select)，此时select 可以正常执行，并等待统计结果(此时表的data_object_id还未发生变化)

第二：需要alter move操作在 select操作之前完成，这样确保select操作过程经历两个不同的data_object_id

为了尽量避免该问题的产生尽量避免在业务繁忙的时候进行操作。关于ORA-8103的更多信息，请查看

OERR: ORA-8103 "object no longer exists" Master Note / Troubleshooting, Diagnostic and Solution (Doc ID 8103.1)