诗檀软件专业数据库修复团队
ora-01499
oerr ora 1499
01499, 00000, "table/index cross reference failure - see trace file"
// *Cause:
// *Action:
适用于：
Oracle Database - Enterprise Edition – 版本 8.1.7.0 到12.1.0.1 [Release 8.1.7 to 12.1]
本文信息适用于任何平台。
Oracle Server Enterprise Edition – 版本: 8.1.7.0 到 11.2.0.2
症状
使用"validate structure cascade"分析表失败，生成ORA-1499 且跟踪文件包含信息 "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
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
该逻辑不一致问题也能显示为10g+中ORA-600 [kdsgrp1] 或更低版本中的ORA-600 [12700]。
原因
在表和其索引间有逻辑不一致性。这种类型的逻辑不一致性通常是由于表中的High Water Mark (HWM) 的问题，其中全表扫描返回的行可能比索引扫描的少。
不一致性可能由Oracle defect 导致或由于LOST IO引起的OS/硬件问题。
解决方案
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(<tablename> <indexname identified in 2.1>) */ rowid
, 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>;
示例：
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;
全表扫描丢失的值可以使用下一个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;
/
解决方案
- 当索引的行比表少，重建索引可能解决该问题。
- 当索引的行比表多，重建索引或通过运行dummy 插入到表来提高HWM可能最终修复该逻辑损坏。在这种情况下，逻辑损坏被修复但这些行可能"lost"，可以在应用这些解决方法之前使用以上"SCRIPT 1"来恢复它们。
- 为了避免该不一致性，查看以下已知问题的列表。
如果需要Oracle Support的额外帮助，请提供：
1. analyze 命令生成的跟踪文件。
2. 以上"QUERY 1" 的结果。
3. 通过dump基表的段头生成的跟踪文件：
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.
- 为索引检索的额外行识别extent id's 的查询结果：
select rid, a.relative_fno, a.block, e.owner, e.segment_name, e.segment_type, e.extent_id, e.blocks
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);
注：
- 用适当值替换owner, table name, index name 和indexed column。
- tablespace_name 是以上步骤3中提供的。
- 该查询提供了由索引检索行位于的extent 。
已知问题:
要避免该问题，参考
Note 1499.1 获取已知的Oracle 缺陷。对于由OS/硬件引起的LOST IO，联系第三方供应商。
参考
NOTE:1554054.1 - Quick method to identify table/index mismatch when analyze validate structure cascade takes significant time