ORA-1499. Table/Index row count mismatch [ID 563070.1]

renjixinchina發表於2013-01-04

In this Document
Symptoms
Cause
Solution
References

Applies to:

Oracle Server - Enterprise Edition - Version: 8.1.7.0 to 11.1.0.7 - Release: 8.1.7 to 11.1
Oracle Server - Enterprise Edition - Version: 8.1.7.0 to 11.1.0.7 [Release: 8.1.7 to 11.1]
Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 8.1.7.0 to 11.2.0.2

***Checked for relevance on 15-Sep-2011***

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

Cause

There is a logical inconsistency between a table and its index. This kind of logical inconsistencies is commonly due to a problem with the High Water Mark (HWM) in the table where a full table scan may return less rows than an Index Scan.

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

Solution

The rows retrieved through the index that are not retrieved by a full table scan can be identified by running this query:

select /*+ INDEX_FFS( ) */ rowid
, dbms_rowid.ROWID_RELATIVE_FNO(rowid) relative_fno
, dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block
from
where is not null
minus
select /*+ FULL()*/ rowid
, dbms_rowid.ROWID_RELATIVE_FNO(rowid) relative_fno
, dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block
from ;

Example:

select /*+ INDEX_FFS(TEST I_TEST) */ rowid
, dbms_rowid.ROWID_RELATIVE_FNO(rowid) relative_fno
, dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block
from test
where c2 is not null
minus
select /*+ FULL(TEST)*/ rowid
, dbms_rowid.ROWID_RELATIVE_FNO(rowid) relative_fno
, dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block
from test;

The missing values from a full table scan can be stored in another table using the index with the next plsql (SCRIPT. 1):

drop table test_copy;

create table test_copy as select * from test where 1=2;

declare
cursor missing_rows is
select /*+ INDEX_FFS(TEST I_TEST) */ rowid rid
from test
where c2 is not null
minus
select /*+ FULL(TEST)*/ rowid rid
from test;
begin
for i in missing_rows loop
insert into TEST_COPY
select /*+ ROWID(TEST) */ * from TEST where rowid = i.rid;
end loop;
end;
/

SOLUTION

- When the index has less rows than the table, recreating the index may solve this problem.

- When the index has more rows than the table, recreating the index or raising the HWM by running dummy inserts into the table may eventually fix this logical corruption. In this case the logical corruption is fixed but these rows may be "lost" for which there is the option to recover them by using "SCRIPT. 1" above before applying any of these solutions.

- To prevent this inconsistency see for the list of Known Issues below.

If additional help is required from Oracle Support, please provide:

1. The trace file generated by the analyze command.
2. The result of "QUERY 1" above.
3. The trace file generated by dumping the segment header for the base table:

select header_file, header_block, tablespace_name
from dba_segments
where wner = upper('&table_owner') and segment_name = upper('&table_name');

alter system dump datafile &header_file block &header_block;

This trace file will be used to identify the HWM.

@ Example from a block dump not using ASSM (Automatic Segment Space Management):
@
@ Extent Control Header
@ -----------------------------------------------------------------
@ Extent Header:: spare1: 0 spare2: 0 #extents: 4 #blocks: 31
@ last map 0x00000000 #maps: 0 offset: 4128
@ Highwater:: 0x014000d6 ext#: 3 blk#: 5 ext size: 8
@
@ So, HWM is located at RDBA 0x014000d6 in extent_id=5 and block#=5 in that extent.

4. The result of this query to identify the extent id's for the additional rows retrieved by the index:

select rid, a.relative_fno, a.block, e.owner, e.segment_name, e.segment_type, e.extent_id, e.blocks
from (select /*+ INDEX_FFS() */ rowid rid
, dbms_rowid.ROWID_RELATIVE_FNO(rowid) relative_fno
, dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block
from

where is not null
minus
select /*+ FULL(

)*/ rowid rid
, dbms_rowid.ROWID_RELATIVE_FNO(rowid) relative_fno
, dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block
from

) a
, dba_extents e
where a.relative_fno=e.relative_fno
and e.tablespace_name = upper('&tablespace_name')
and v.ts#=&tablespace_number
and (a.block between e.block_id and e.block_id+blocks-1);

Notes:

- Replace owner, table name, index name and indexed column by the appropriate values.
- Tablespace_name is the one provided in step 3 above.
- This query provides the extent where the row retrieved by the index is located.

Known Issues:

Reference Note 1499.1 for known issues.