数据库强制打开后,发现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:
-- 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 问题解决。 |