ORA-8103 “object no longer exists“错误 Oracle

一、错误原因及解决方案 OERR: ORA-8103 "object no longer exists" Master Note / Troubleshooting, Diagnostic and Solution (Doc ID 8103.1)上的这篇文档给我们清楚的列出了可能导致该报错的原因(该报错有多种可能，我只简单的讲一下我目前碰到的情况)
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.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.
ORA-8103也可能是由于涉及到相关对象的sql已经执行，但是在sql操作完成之前，对象的data_object_id 发生了变化导致的（data_object_id 可能会因如下操作发生变化：truncate table; alter index ... rebuild; alter table move; ），truncate一张空表并不会导致表的data_object_id发生变化
看到这，我已经找到了系统中产生报错的原因，我们一直在压缩一些历史的分区表，研发报错的时候压缩脚本正在执行。

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 表

-- 注意在move动作还未完成之前，表的data_object_id不会发生变化 alter table DIM_SERVICE_UA_150107HIS move;

3. 访问表
2和3顺序可以对调，但要确保执行count时间长于move操作（保证elect操作过程经历两个不同的data_object_id）

select count(10) from DIM_SERVICE_UA_150107HIS,DIM_SERVICE_UA_150107HIS;

4. 当move操作完成时，count操作紧跟着会立刻报错

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

【ORA-8103 “object no longer exists“错误】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