ORA-12012: error on auto execute of job 4002
ORA-08102: index key not found, obj# 289, file 1, block 2033
(2)
Errors in file
/u01/app/oracle/diag/rdbms/oblprod/oblprod/trace/oblprod_j001_28452.trc:
select *
from dba_objects where object_id=289;
SYS
I_JOB_NEXT 289
289 INDEX
Now the index I_JOB_NEXT
seems to be corrupted at block# 2033. This belongs to the job$ table. Let’s try
to run an ANALYZE on the table.
SQL> ANALYZE TABLE JOB$ VALIDATE STRUCTURE CASCADE;
ANALYZE TABLE JOB$ VALIDATE STRUCTURE CASCADE
*
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace
file
By now, we know that index
i_job_next is created on the next_date column of job$ table.
So, let’s try to do an explicit full table scan on job$.
So, let’s try to do an explicit full table scan on job$.
SELECT
/*+ FULL(j1) */ next_date
FROM
job$ j1
MINUS
SELECT
/*+ index(j i_job_next) */ next_date
FROM
job$ j;
Surprisingly, doing
the above FTS followed with an ANALYZE table command did the trick for me and
the index is reusable now.
SQL> ANALYZE TABLE
JOB$ VALIDATE STRUCTURE CASCADE;
Table analyzed.
