某个应用最近总出现死锁,其中一些是因为报了索引和数据行存在不匹配的问题,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>selectowner,segment_name,segment_type from dba_segments whereheader_file=5 andheader_block=145;
-----------------------------------------
SCOTTI_TEST INDEX
,dbms_rowid.ROWID_RELATIVE_FNO(rowid)relative_fno
,dbms_rowid.ROWID_BLOCK_NUMBER(rowid)block
from<tablename>
where<indexedcolumn>isnotnull
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
fromtest
wherec2isnotnull
minus
select/*+FULL(TEST)*/rowid
,dbms_rowid.ROWID_RELATIVE_FNO(rowid)relative_fno
,dbms_rowid.ROWID_BLOCK_NUMBER(rowid)block
fromtest;
createtabletest_copyasselect*fromtestwhere1=2;
declare
cursormissing_rowsis
select/*+INDEX_FFS(TESTI_TEST)*/rowidrid
fromtest
wherec2isnotnull
minus
select/*+FULL(TEST)*/rowidrid
fromtest;
begin
foriinmissing_rowsloop
insertintoTEST_COPY
select/*+ROWID(TEST)*/*fromTESTwhererowid=i.rid;
endloop;
end;
/
altersystemdumpdatafile&header_fileblock&header_block;
@
@ExtentControlHeader
@-----------------------------------------------------------------
@ExtentHeader::spare1:0spare2:0#extents:4#blocks:31
@lastmap0x00000000#maps:0offset:4128
@Highwater::0x014000d6ext#:3blk#:5extsize:8
@
@So,HWMislocatedatRDBA0x014000d6inextent_id=5andblock#=5inthatextent.
from(select/*+INDEX_FFS(<tablename><indexname>)*/rowidrid
,dbms_rowid.ROWID_RELATIVE_FNO(rowid)relative_fno
,dbms_rowid.ROWID_BLOCK_NUMBER(rowid)block
from<tableowner.tablename>
where<indexedcolumn>isnotnull
minus
select/*+FULL(<tablename>)*/rowidrid
,dbms_rowid.ROWID_RELATIVE_FNO(rowid)relative_fno
,dbms_rowid.ROWID_BLOCK_NUMBER(rowid)block
from<tableowner.tablename>
)a
,dba_extentse
wherea.relative_fno=e.relative_fno
ande.tablespace_name=upper('&tablespace_name')
andv.ts#=&tablespace_number
and(a.blockbetweene.block_idande.block_id+blocks-1);