Oracle ORA-1499 table/Index Cross Reference Failure

ORA-1499 table/Index Cross Reference Failure - see trace file (Doc ID 1499.1)
详情:

Error: ORA 01499
Text: table/Index Cross Reference Failure - see trace file
-------------------------------------------------------------------------------
Cause:  An error occurred when validating an index or a table using the
        ANALYZE command.
        One or more entries does not point to the appropriate cross-reference.
Action: Check the trace file for more descriptive messages about the problem.
        Correct these errors.

描述:

Error ORA-1499 is produced by statement "ANALYZE TABLE|CLUSTER <name> VALIDATE STRUCTURE CASCADE" to report an inconsistency between a table or a cluster and its index where an index key value is not found in the index or vice versa.

The content of the trace file has:
<description>: tsn: <tablespace number> rdba: <relative dba>

description:

"row not found in index"
"Table/Index row count mismatch"
"row mismatch in index dba"
"Table row count/Bitmap index bit count mismatch"
"kdavls: kdcchk returns %d when checking cluster dba 0xlx objn %d\n"

tsn:    Tablespace Number where the INDEX is stored.
rdba: Relative data block address of the INDEX segment header.

例如:

SQL> analyze table DEPT validate structure cascade;
analyze table dept validate structure cascade
*
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file

trace文件内容:
row not found in index tsn: 5 rdba: 0x02c00061

定位哪些索引受到影响:

The trace file for ORA-1499 provides the rdba for the segment header related to the index. 
Query dba_segments to identify the index

SQL语句:
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'));

The &rdba value should be the rdba in hexadecimal above 
removing the '0x' portion and tsn is the tablespace number. Example:

将tsn与rdba代入:
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('02c00061','XXXXXXXX'))
                       AND  ts#= 5)
   AND header_block = dbms_utility.data_block_address_block(to_number('02c00061','XXXXXXXX'));

定位受影响的键值:

Rows in the table that are not in the index:
SELECT /*+ FULL(t1) */ rowid, <indexed column list>
FROM   <Table name> t1
MINUS
SELECT /*+ index(t <Index name>) */ rowid, <indexed column list>
FROM   <Table name> t;

Rows in the index that are not in the table:
SELECT /*+ index(t <Index name>) */ rowid, <indexed column list>
FROM   <Table name> t
MINUS
SELECT /*+ FULL(t1) */ rowid, <indexed column list>
FROM  <Table name> t1

例如：
Table name = DEPT, Index name = I_DEPT1, Indexed columns in index I_DEPT1 are: DEPTNO, DNAME.

SELECT /*+ FULL(t1) */ rowid, deptno, dname
FROM   dept t1
MINUS
SELECT /*+ index(t I_DEPT1) */ rowid, deptno, dname
FROM   dept t;

Cause:
The root cause for an inconsistency between the table and its index can be and Oracle defect, a problem external to Oracle like Lost IO or the use of non-deterministic function in a function-based index:

Solution:
For message “Table/Index row count mismatch” reference Note 563070.1
When an inconsistency is identified between the table and the index where the row is not found in the index, dropping and recreating the index can be the appropriate action most of the times.