学习：ORA-8103 Object No Longer Exists

一、报错

有个朋友遇到一个存储过程，开发反馈执行有问题，需要排查，后续检查发现这个JOB调用执行存储过程报对象不存在？？？ 需要对这个问题进行分析一波
ORA-8103 "Object No Longer Exists"

二、相关资料

问题很简单对象不存在？ 真的不存在吗？ 对象检查还是存在的！那什么情况会出现对象不存在的问题？？？

参考链接
Solution: 'ORA-8103: Object no longer exists' When Insert Into External Table after Truncate With Storage Performed (Doc ID 422083.1)    
OERR: ORA-8103 "Object No Longer Exists" Master Note / Troubleshooting, Diagnostic and Solution (Doc ID 8103.1)    
膜拜下面两位大神的博客

http://blog.itpub.net/29863023/viewspace-2662449/
https://www.askmaclean.com/archives/ora-8103.html
如果执行 flush buffer cache之后再次analyze validate structure不再报ORA-8103错误则说明：
可能是完全正常的现象，之前的ORA-8103正是也因为对象正在被DROP/TRUNCATE而导致SELECT报ORA-8103。一般来说Call Stack会显示进程正尝试访问该段的segment header。 
更多信息可以参考BUG 7441661
也可能该问题仅仅发生在buffer cache层，而没有发生在DISK上。通过flush buffer_cache若能解决，则一般是这种情况，往往是Buffer Cache管理的BUG 。





ORA-8103 is also caused by an unexpected data_object_id where it is concurrently changing for the involved object while the affected SQL statement 
is executed.

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
alter table .. exchange partition
alter table .. split partition
etc.
For a truncate look for column TRUNCATED in DBA_TAB_MODIFICATIONS. Note that it indicates whether the table has been truncated since the last
 analyze.  See documentation.
In 11g+ parameter enable_ddl_logging can be set to TRUE to print DDL statements in the alert log or in 12c in the log/ddl sub-directory of the
 ADR home (example <diagostic_dest>/rdbms/<sid>/<dbname>/log/ddl_$ORACLE_SID.log) and identify what DDL's are run that may potentially cause
 this error.   The init.ora parameter ENABLE_DDL_LOGGING is licensed as part of Oracle Database Lifecycle Management Pack for Oracle Database
 when set to TRUE.  For more information check the "Database Licensing Information User Manual" section "Oracle Database Editions".

三、相关可能性太多！

观察JOB调用执行的存储过程，存在truncate，与文档中的

--session 1 select object 
--session 2 truncate
--session 1 返回ORA报错对象不存在

模拟！
SQL> select sum(bytes)/1024/1024/1024 from user_segments where segment_name='RANGE_PART_TAB';
SUM(BYTES)/1024/1024/1024
-------------------------
               2.93066406

SQL> set timing on
SQL> set autotrace on
SQL> select count(*) from RANGE_PART_TAB;

SQL> truncate table scott.RANGE_PART_TAB;

                     *
ERROR at line 1:
ORA-08103: object no longer exists


SQL> select object_id,DATA_OBJECT_ID from dba_objects where owner='SCOTT' AND OBJECT_NAME='TT';
SQL> CREATE TABLE TT(ID INT);
SQL> select object_id,DATA_OBJECT_ID from dba_objects where owner='SCOTT' AND OBJECT_NAME='TT';
 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     89382          89382
SQL> TRUNCATE TABLE TT;
SQL> select object_id,DATA_OBJECT_ID from dba_objects where owner='SCOTT' AND OBJECT_NAME='TT';
 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     89382          89382
     
SQL> INSERT INTO TT VALUES(1);
SQL> COMMIT;
SQL>  TRUNCATE TABLE TT;
 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     89382          89383
     
SQL> INSERT INTO TT VALUES(1);
SQL> COMMIT;
SQL>  TRUNCATE TABLE TT reuse storage;

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     89382          89384


将情况反馈给开发，开发可以从几个方面调整
1.truncate与select 代码错开；
2.如果select truncate的对象报错，ORA-8103 再次查询，而非结束存储过程异常退出；
下一次再次执行可以加上 sleep 5s 再次查询一次,truncate一个对象2~3s就完成了。或者sleep更久