ORA-1499. Table/Index row count mismatch (Doc ID 563070.1)

Last updated on JULY 05, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 8.1.7.0 to 12.1.0.2 [Release 8.1.7 to 12.1]
Information in this document applies to any platform.

Symptoms

Analyze table with "validate structure cascade" fails with ORA-1499 and the trace file contains message "Table/Index row count mismatch". Example:

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

 

The associated trace file contains:

Table/Index row count mismatch
table 6559 : index 10000, 0
Index root = tsn: 6 rdba: 0x01400091

It means: A table scan returned 6559 rows and an index scan returned 10000 rows.

"Index root" is the segment header information for the index:

rdba: 0x01400091 is the Index segment header relative data block address. It is decimal 20971665 which is Rfile#=5 Block#=145 :

SQL> select dbms_utility.data_block_address_file(20971665)  "Rfile#"  
2          ,dbms_utility.data_block_address_block(20971665) "Block#"  
3 from dual;  

Rfile#     Block#  
---------- ----------  
5          145  


Running the next query can identify the associated index:

QUERY 1: 

SQL> select owner, segment_name, segment_type 
2    from  dba_segments 
3    where header_file = 5 
4      and header_block = 145; 

OWNER    SEGMENT_NAME    SEGMENT_TYPE 
-------- --------------- ------------------ 
SCOTT    I_TEST          INDEX 
 

This logical inconsistency can also be manifested with an ORA-600 [kdsgrp1] in 10g+ or ORA-600 [12700] in lower versions.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms