某个应用最近总出现死锁，其中一些是因为报了索引和数据行存在不匹配的问题，MOS中有如下文档可以参考。
ORA-1499. Table/Index row count mismatch(文档 ID 563070.1)
现象：
使用“validate structure cascade”分析表时报ORA-1499的错误，trace文件中包含“Table/Index row count mismatch”的错误信息。例如：
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
trace文件中包含：
Table/Index row count mismatch
table 6559 : index 10000, 0
Index root = tsn: 6 rdba: 0x01400091
意味着扫描表返回6559行数据，索引扫描返回10000行数据。“Index root”是索引的段头信息。rdba: 0x01400091是相对于数据块地址的索引段头。他是十进制的20971665，Rfile#=5，Block#=145。
SQL> select dbms_utility.data_block_address_file(20971665) "Rfile#" ,dbms_utility.data_block_address_block(20971665) "Block#" from dual;
Rfile# Block#
---------- ----------
5 145
运行下面的查询明确关联的索引：
SQL> select owner, segment_name, segment_type from dba_segments where header_file = 5 and header_block = 145;
-------- --------------- ------------------
SCOTT I_TEST INDEX
, dbms_rowid.ROWID_RELATIVE_FNO(rowid) relative_fno
, dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block
from <tablename>
where <indexed column> is not null
minus
select /*+ FULL(<tablename>)*/ rowid
, dbms_rowid.ROWID_RELATIVE_FNO(rowid) relative_fno
, dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block
from <tablename>;
实例：
, 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;
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;
/
alter system dump datafile &header_file block &header_block;
@
@ 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.
from (select /*+ INDEX_FFS(<table name> <index name>) */ rowid rid
, dbms_rowid.ROWID_RELATIVE_FNO(rowid) relative_fno
, dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block
from <table owner.table name>
where <indexed column> is not null
minus
select /*+ FULL(<table name>)*/ rowid rid
, dbms_rowid.ROWID_RELATIVE_FNO(rowid) relative_fno
, dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block
from <table owner.table name>
) 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);
