ORA-08103错误原理及解决方案

来源：互联网发布：淘宝官方企业店旗舰店编辑：程序博客网时间：2024/06/08 06:44

java后台报错信息：

### Cause: java.sql.SQLException: ORA-08103: 对象不再存在at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:104)at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:95)at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:59)

原因：

SQL> !oerr ora 08103
08103, 00000, "object no longer exists"
// *Cause: The object has been deleted by another user since the operation
//          began, or a prior incomplete recovery restored the database to
//          a point in time during the deletion of the object.
// *Action: Delete the object if this is the result of an incomplete
//          recovery.

当一个session查询的时候，查询没有结束的时候，另外一个session在truncate表的时候，会导致数据块中的data_object_id 和数据字典中的data_object_id不匹配，进而报错。

oracle中一般情况下表的OBJECT_ID与DATA_OBJECT_ID是一致的，但在truncate后表达DATA_OBJECT_ID会发生改变。

SQL> col object_name for a40;

SQL> set linesize 500;

SQL> select object_name,object_type,object_id,data_object_id from user_objects where object_name='TT1';

OBJECT_NAME               OBJECT_TYPE          OBJECT_ID DATA_OBJECT_ID
---------------------------------------- ------------------- ---------- --------------
TT1                   TABLE           514640    514640

SQL> truncate table tt1;

Table truncated.

SQL> select object_name,object_type,object_id,data_object_id from user_objects where object_name='TT1';

OBJECT_NAME               OBJECT_TYPE          OBJECT_ID DATA_OBJECT_ID
---------------------------------------- ------------------- ---------- --------------
TT1                   TABLE           514640    514640

SQL> insert into tt1 values(123);

1 row created.

SQL> commit;

Commit complete.

SQL> select object_name,object_type,object_id,data_object_id from user_objects where object_name='TT1';

OBJECT_NAME               OBJECT_TYPE          OBJECT_ID DATA_OBJECT_ID
---------------------------------------- ------------------- ---------- --------------
TT1                   TABLE           514640    514640

SQL> truncate table tt1;

Table truncated.

SQL> select object_name,object_type,object_id,data_object_id from user_objects where object_name='TT1';

OBJECT_NAME               OBJECT_TYPE          OBJECT_ID DATA_OBJECT_ID
---------------------------------------- ------------------- ---------- --------------
TT1                   TABLE           514640    514641

其实object_id和data_object_id同样是表示数据库对象的一个唯一标志，但是object_id表示的是逻辑id，data_object_id表示的是物理id。当表刚创建的时候它的object_id和data_object_id都是相等的，但是如果表经过move或truncate后那么data_object_id将会有变化。

解决办法：

1、用delete代替truncate

2、用select for updaate 查询的时候，将表锁住，不然其他session将表截断。

0 0