ORA-08102: index key not found

数据库强制打开后，发现job无法执行

错误信息如下 ORA-08102: index key not found，obj#239，file 1，block 1674(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 问题解决。