ORA-08102: index key not found, obj# 129038, file 78, block 2910787 (2)

表的索引发生发生问题

 

查询发现obj#239是索引I_JOB_NEXT 。

 

SQL> col object_name format a15;

SQL> col owner format a10;

SQL> select o.owner, o.object_name, o.object_id, o.object_type
  2    from dba_objects o
  3   where o.object_id = 239;

OWNER      OBJECT_NAME      OBJECT_ID OBJECT_TYPE
----------    ---------------           ----------      -------------------
SYS           I_JOB_NEXT             239              INDEX

 

 

那么I_JOB_NEXT 是那个表的索引呢？原来是 JOB$的索引。

 

SQL> select i.owner, i.index_name, i.table_name
  2    from dba_indexes i
  3   where i.index_name = 'I_JOB_NEXT';

OWNER      INDEX_NAME    TABLE_NAME
----------      ----------            ----------
SYS             I_JOB_NEXT     JOB$

 

 

下面看看索引I_JOB_NEXT 是建立在那个字段上的

SQL> col index_name format a10;
SQL> col index_owner format a10;
SQL> col table_name format a10;
SQL> col column_name format a15;

 

SQL>  select INDEX_OWNER, INDEX_NAME, TABLE_NAME, COLUMN_NAME
  2     from dba_ind_columns
  3    where INDEX_NAME = 'I_JOB_NEXT';

 

INDEX_OWNE   INDEX_NAME     TABLE_NAME      COLUMN_NAME
----------             ----------              ----------             ---------------
SYS                    I_JOB_NEXT       JOB$                   NEXT_DATE

 

 

 

 

既然ORA-08102: index key not found，我们尝试来rebuild一下索引，看看效果如何。

SQL>   alter index I_JOB_NEXT rebuild;

Index altered

 

问题依旧，去metalink上碰碰运气，发现了这篇文章中有类似的案例。

ORA-08102: TRYING TO MANIPULATE A JOB IN DBA_JOBS [ID 1036858.6]

Solution Description:
=====================

You need to recreate the inex I_JOB_NEXT.

Script "$ORACLE_HOME/rdbms/admin/cat7103.sql" creates the I_JOB_NEXT:

Drop and recreate this index.

connect sys/<password>
drop index i_job_next;
create index i_job_next on job$ (next_date)

Note: alter index I_JOB_NEXT rebuild;

      Will not fix the problem.

 

 

-- 1.先删除索引i_job_next

SQL> drop index i_job_next;

Index dropped

 

--2.重建索引i_job_next;

SQL> create index i_job_next on job$ (next_date);

Index created

问题解决。

 

 

 

 

[java] org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL []; SQL state [72000]; error code [8102]; ORA-08102: index key not found, obj# 129038, file 78, block 2910787 (2)

     [java] ; nested exception is java.sql.SQLException: ORA-08102: index key not found, obj# 129038, file 78, block 2910787 (2)
     [java]
     [java]     at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
     [java]     at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
     [java]     at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
     [java]     at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:607)
     [java]     at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:792)
     [java]     at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:815)
     [java]     at com.xiu.search.good.service.CatentryBaseService.deleteGoodBasicInfo(CatentryBaseService.java:225)
     [java]     at com.xiu.search.good.manager.CatentryManager.cleanData(CatentryManager.java:30)
     [java]     at com.xiu.search.good.GoodSearchIndexHelper.updateSearchIndexes4GoodPriceChanges(GoodSearchIndexHelper.java:117)
     [java]     at com.xiu.search.good.UpdateData.run(UpdateData.java:84)
     [java]     at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:897)
     [java]     at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:919)
     [java]     at java.lang.Thread.run(Thread.java:736)
     [java] Caused by: java.sql.SQLException: ORA-08102: index key not found, obj# 129038, file 78, block 2910787 (2)
     [java]
     [java]     at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
     [java]     at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
     [java]     at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
     [java]     at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
     [java]     at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:219)
     [java]     at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:970)
     [java]     at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1190)
     [java]     at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3370)
     [java]     at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3454)
     [java]     at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:798)
     [java]     at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:591)
     [java]     ... 9 more