These errors flooded the database alert log. Thankfully, Oracle was kind enough to specify the object number (obj# or object id) which permitted ease of locating the "challenged" object.
This simple SQL statement lead me to the object.
(290 is the one shown in my alert log.)
SQL> select object_name, object_type, status from dba_objects where object_id='290';
OWNER
-------------------------------------
OBJECT_NAME
------------------------------------------------------------------------------------
OBJECT_TYPE STATUS
----------------------------------------------- ------------
SYS
I_JOB_NEXT
INDEX VALID
I checked further to determine the table which the index is built on.
SQL> select table_name, table_owner from dba_indexes where index_name='I_JOB_NEXT';
TABLE_NAME TABLE_OWNER
------------------------------------ -------------------------------------
JOB$ SYS
Next is to analyze the table/index to determine if there is a sort of corruption
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
Solution:
Recreate the table job$ by create a backup table and reload the data into it.
1.SQL> create table job1 as select * from job$;
2.SQL> truncate table job$;
3.SQL> insert into job$ select * from job1;
4. SQL> commit;
5. SQL> analyze table job$ validate structure cascade;
The errors stoped after a successful validation.
In other cases, one may still get the error after following the above steps. In that case the indexes should be recreated.
Rebuild the two indexes on table job$
SQL> alter index I_job_job rebuild online;
SQL> alter index I_job_next rebuild online;
SQL> analyze table job$ validate structure cascade;
Thank you. That worked for me !!.
ReplyDeleteThanks, worked nicely.
ReplyDelete