My Oracle 11.2.0.4 database started reporting Ora-600 errors
Upon checking the alert log and Oracle Support (Doc ID 1438920.1) ,
Turns out the Index was corrupted.. Ran the Analyze table command to analyze the problem table
SQL> analyze table APP_USER.ENTRY_HIT VALIDATE STRUCTURE CASCADE;
analyze table APP_USER.ENTRY_HIT VALIDATE STRUCTURE CASCADE
*
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file
Trace file shows below
Table/Index row count mismatch
table 384001 : index 384028, 0
Index root = tsn: 7 rdba: 0x02400a5a
The error says the indexes are still bad.
If you run the query through SQL*PLUS it will ask for a rdba number. Enter the value from error message '0x02400a5a' (no quotes). This will return a file number and a block number.
SQL> SELECT dbms_utility.data_block_address_file(
2 to_number(trim(leading '0' from
3 replace('&&rdba','0x','')),'XXXXXXXX')
4 ) AS rfile#,
5 dbms_utility.data_block_address_block(
to_number(trim(leading '0' from
6 7 replace('&&rdba','0x','')),'XXXXXXXX')
8 ) AS block#
9 FROM dual;
Enter value for rdba: 0x02400a5a
old 3: replace('&&rdba','0x','')),'XXXXXXXX')
new 3: replace('0x02400a5a','0x','')),'XXXXXXXX')
old 7: replace('&&rdba','0x','')),'XXXXXXXX')
new 7: replace('0x02400a5a','0x','')),'XXXXXXXX')
RFILE# BLOCK#
---------- ----------
9 2650
Next run the following query:
select owner, segment_name, segment_type
from dba_segments
where header_file = <rfile#>
and header_block = <block#>;
This will give you the offending index to be dropped and recreated.
