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

最新推荐文章于 2024-07-25 08:45:00 发布

原创最新推荐文章于 2024-07-25 08:45:00 发布 · 2k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#file #java #sql #object #table #exception

本文详细描述了如何在Oracle数据库中解决索引问题，包括查询索引详情、重建索引以及解决与特定任务相关的错误。通过实例分析，展示了从查询到解决问题的全过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

表的索引发生发生问题

查询发现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