My Oracle Support Banner

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

Last updated on OCTOBER 26, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 8.1.7.0 to 12.2.0.1 [Release 8.1.7 to 12.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
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 (relative file number) Block#=145 :

prompt Enter tsn:
accept tsn

prompt Enter rdba (in hex, no '0x' prefix):
accept hex_rdba

set verify off

select file#,
dbms_utility.data_block_address_block(
to_number('&hex_rdba','XXXXXXXXX')) block#
from sys.v$datafile
where ts# = &tsn
and rfile# = dbms_utility.data_block_address_file(
to_number('&hex_rdba','XXXXXXXXX'));

 

FILE#      BLOCK#  
---------- ----------  
7          145 


Running the next query from dba_segments identifies the associated index (it needs the absolute file# for header_file):

QUERY 1: 

SQL> select owner, segment_name, segment_type 
2    from  dba_segments 
3    where header_file = 7 
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

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Cause
Solution
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.