ORA-01499: table/index cross reference failure

If there is a situation where the data is not being found using certain WHERE clauses (for example WHERE id = ?), but could be found in some other ways using some other columns (for example WHERE first_name = ? AND last_name = ?), this could be an issue with index corruption.

Root cause: There was a row (or a set of rows) in the table without a corresponding index entry. This could be due to oracle bug or lost write condition (h/w & OS involved). If it happens more than once, Oracle Support should be contacted - there are tons of bug related to this in MOS. 


Some real life example below which I encountered recently -


++ Try to select the record 

I knew there is a row with ID = 372876, but was not able to find that using ID in WHERE clause.

SQL> SET AUTOTRACE ON

SQL> SELECT rowid, id FROM client t1 WHERE id = 372876;

no rows selected


Execution Plan

-----------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 17 | 2 (0)| 00:00:01 |

|* 1 | INDEX UNIQUE SCAN| PK_CLIENT_OVERSIGHT | 1 | 17 | 2 (0)| 00:00:01 |

-----------------------------------------------------------------------------------------


++ Force full table scan, avoiding the index scan 

SQL> SELECT /*+ FULL(t1) */ rowid, id

FROM client t1 WHERE id = 372876;


ROWID                                        ID

------------------                             ----------

AAAU3uAABAAKSexAAD      372876


Execution Plan

--------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 17 | 384 (6)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| CLIENT | 1 | 17 | 384 (6)| 00:00:01 |

--------------------------------------------------------------------------------------


++ Now do a corruption check including indexes in the table 

SQL> ANALYZE TABLE PROD.CLIENT VALIDATE STRUCTURE CASCADE;

*

ERROR at line 1:

ORA-01499: table/index cross reference failure - see trace file

++ Look at the trace file to get more info

trace file: row not found in index tsn: 15 rdba: 0x09cad60a

++ Use SQL to find the object/segment/indexes

SELECT owner, segment_name, segment_type, partition_name

FROM   DBA_SEGMENTS

WHERE  header_file = (SELECT file# 

                      FROM   v$datafile 

                      WHERE  rfile# = dbms_utility.data_block_address_file(to_number('&rdba','XXXXXXXX'))

                        AND  ts#= &tsn)

  AND header_block = dbms_utility.data_block_address_block(to_number('&rdba','XXXXXXXX'));

Here, rdba=09cad60a (excluding first 2 digits) and tsn= 15 

Solution:


++ Drop index or affected indexes, then recreate. DO NOT rebuild. Dropping and recreating index forces Oracle to recreate the index from the table data.

++ Hold on, there might be cases that, after dropping and recreating index did not resolve the issue, there might be more erros like below when you do DML on the table -

ORA-08102: index key not found, obj# 128201, file 39, block 627105 (2)

In that case, just recreate the table using CREATE TABLE .. AS .. or expdp/impdp or online redefinition.

***