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

renjixinchina發表於2013-01-04
Index

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  owner = 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); 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15747463/viewspace-752092/，如需轉載，請註明出處，否則將追究法律責任。

0
0
上一篇： ORACLE 統計資訊的收集與管理
下一篇： [sql 最佳化]使用TKPROF格式化TRACE輸出
ORA-1499. Table/Index row count mismatch [ID 563070.1]
請登入後發表評論 登入
全部評論
renjixinchina

註冊時間：2011-01-30

  • 博文量
    373
  • 訪問量
    2107713

最新文章

支援我們 作者招募 使用者協議 FAQ Contact Us

北京盛拓優訊資訊科技有限公司. 版權所有  京ICP備16024965號-8 北京市公安局海淀分局網監中心備案編號：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人舉報專區

廣播電視節目製作經營許可證(京) 字第1234號 中國網際網路協會會員

相關文章