[oracle@mlab2 ~]$ oerr ora 8102
08102, 00000, "index key not found, obj# %s, file %s, block %s (%s)"
// *Cause:  Internal error: possible inconsistency in index
// *Action:  Send trace file to your customer support representative, along
//           with information on reproducing the error


ORA-8102错误出现的原理是当表或者LOB SEGMENT上存在一个键值,但是该键值在索引上却找不到时,则出现错误。




oer 8102.<code> - obj# <object id>, rdba: <rdba value>(afn <file#>, blk# <block#>)
kdk key 8102.2:
ncol: <number of columns in the key including the rowid>, len: <key length>
key: (<length>):<hexadecimal value>



其中 obj#为 受影响对象的object_id, rdba为相对数据块地址,AFN为绝对文件号,blk#为 该key应当存放在的索引的块号。



SQL> DELETE dept WHERE deptno=10;
DELETE dept WHERE deptno=10
ERROR at line 1:
ORA-08102: index key not found, obj# 46115, file 5, block 90 (2)

oer 8102.2 - obj# 46115, rdba: 0x02c0005a(afn 5, blk# 90)
kdk key 8102.2:
ncol: 3, len: 16
key: (16):
06 c5 02 01 01 27 02 04 c3 02 32 33 06 02 c0 00 4a 00 05





FROM   dba_objects
WHERE  object_id = 46115;



VALIDATE STRUCTURE CASCADE;命令来验证,如果确实存在表和索引的不一致则会出现ORA-1499错误:



也可以选择 通过全表扫描的结果与索引扫描的结果对比:


SELECT /*+ FULL(t1) */ <indexed column list>
FROM <Table name> t1
SELECT /*+ index(t <Index name>) */ <indexed column list>
FROM <Table name> t;



例如表名 为 DEPT, Index Name 为I_DEPT1, 索引I_DEPT1 上的字段为DEPTNO, DNAME.

SELECT /*+ FULL(t1) */ deptno, dname
FROM dept t1
SELECT /*+ index(t I_DEPT1) */ deptno, dname
FROM dept t;





硬件或者I/O子系统由于丢失写 Lost Write造成块的逻辑上讹误,当一个Lost Io发生,包含对key的修改或者没有写入到ORACLE数据文件上,这即可能发生在表块上也可能发生在索引块上。


对于 ORA-8102的解决




服务热线 : 13764045638   QQ号:47079569    邮箱:service@parnassusdata.com




但是如果确认是表上存在的损坏,则解决方法可以是 单独修复表上的损坏块 或者 考虑重建表。

如果发生错误的是LOB Index,则移动LOB并重建LOB INDEX


alter table &table_owner.&table_with_lob
move LOB (&&lob_column) store as (tablespace &tablespace_name);


NB Bug Fixed Description
14222244, Adding a column with DEFAULT and NOT NULL constraint disabled causes problems – superseded
13073122, ORA-8102 signaled by q000* processes operating on queues with retention
+ 17761775,,, ORA-600 [kclchkblkdma_3] ORA-600 [3020] or ORA-600 [kcbchg1_16] Join of temp and permanent table in RAC might lead to corruption
17449815, ORA-8102 ORA-1499 after ORA-1/ORA-2291 by MERGE with DML ERROR LOGGING
16844448,,, ORA-600 [3020] after flashback database in a RAC
13708951, ORA-8102 on UPDATE statement with subquery for an indexed column
13146182,,,, ORA-1499 ORA-8102 ORA-600 [kdsgrp1] Bitmap Index / Table mismatch
P 12330911 EXADATA LSI firmware for lost writes
11778458, Wrong Results / ORA-1802 on TO_CHAR with CURSOR_SHARING
10633840,,, ORA-1502 on insert statement on INTERVAL partitioned table. ORA-8102 / ORA-1499 Index inconsistency
10245259,, PARALLEL INSERT with +NOAPPEND hint or if PARALLEL INSERT plan is executed in SERIAL corrupts index and causes wrong results
+ 10209232,,,,,, ORA-1578 / ORA-600 [3020] Corruption. Misplaced Blocks and Lost Write in ASM
+ 9734539, ORA-8102 / ORA-1499 corrupt index after update/merge using QUERY REWRITE
+ 9469117,,, Corrupt index after PDML executed in serial. Wrong results. OERI[kdsgrp1]/ORA-1499 by analyze
+ 9231605,,,, Block corruption with missing row on a compressed table after DELETE
+ 8951812, Corrupt index by rebuild online. Possible OERI [kddummy_blkchk] by SMON
8847637, ORA-7445[kxibPut] caused by merge stmt and online index rebuild
8720802,,, Add check for row piece pointing to itself (db_block_checking,dbv,rman,analyze)
+ 8546356,,,, ORA-8102/ORA-1499/OERI[kdsgrp1] Composite Partitioned Index corruption after rebuild ONLINE in RAC
7710827, Index rebuild or Merge partition causes wrong results in concurrent reads instead of ORA-8103
7705591,,,, Corruption with self-referenced row in MSSM tablespace. Wrong Results / OERI[6749] / ORA-8102
+ 17752121,, ORA-600 [kclchkblkdma_3] ORA-600 [3020] RAC diagnostic/fix to avoid a block being modified in Shared Mode and prevent corruption
16922996 ORA-8102 ORA-1499 Internal rollback in Parallel DML may cause index inconsistency
8588540, Corruption / ORA-8102 in RAC with loopback DB links between instances
8514561 ORA-8102 updating a table with function based index and TYPE columns and a TRIGGER
+ 7329252,,, ORA-8102/ORA-1499/OERI[kdsgrp1] Index corruption after rebuild index ONLINE
6057203,, Corruption with zero length column (ZLC) / OERI [kcbchg1_6] from Parallel update
5621677, Logical corruption with PARALLEL update
5181547, Index corruption after insert-only merge /*+ append */ or PDML into table
5179313, INSERT /*append parallel*/ can corrupt an index
4883635, MERGE (with DELETE) can produce wrong results or Logical corruption in chained rows
* 4570793 Index corruption from array inserts (ORA-8102/ORA-1499)
4246090,, IOT corruption from buffered INSERT with function based index (ORA-8102)
3573604, A transported bitmap index can give various OERI errors / ORA-8102
3365045,, Functional index on DATE column can depend on NLS_DATE_FORMAT (ORA-8102 on DML)
3352413,, An ORA-8102 error can occur on ATEMPIND$ during a user UPDATE with CONSTRAINTS
3069818,, Corruption possible modifying a migrated or chained row
2485931, ORA-8102 from IOT DML with concurrent MOVE ONLINE
2293492,, Fatal error during COMMIT / ROLLBACK may cause permanent corruption (eg: ORA-8102)
2511906 ORA-8102 possible on update of IOT
2271722, ORA-8102 possible on update of IOT with OVERFLOW
2165461 Direct load to table with DESCENDING index may cause subsequent ORA-8102 errors
2131767 Parallel create of FUNCTIONAL INDEX on PARTITION table can product corrupt index (ORA-8102)
2456255 ORA-8102 on DELETE from PARTITIONED table with index
1667103, Update of an IOT with CONCATENATION using a SECONDARY index signals ORA-8102
1388843, UNIQUE/PK constraints ENFORCED with NON-UNIQUE COMPRESSED indexes allow duplicates / ORA-8102
536567,,, Corrupt index from PARALLEL Index build/rebuild of CONCAT index if FFS used and leading columns are NULL.


扫码关注dbDao.com 微信公众号:


  1. Steps to Diagnose the issue: 1) Find the index select * from dba_objects where object_id=; 2) Find the table select table_name from dba_indexes where index_name=’object_name from 1′; 3) analyze index validate structure; analyze table validate structure cascade; analyze table validate structure; 4) ORA-8102 trace file will show you at the beginning what row is not found in the index Eg:Excerpt from the ora-8102 trace file:*** SERVICE NAME:(SYS$USERS) 2009-05-05 20:37:21.144 *** SESSION ID:(64.22684) 2009-05-05 20:37:21.144 oer 8102.2 – obj# 61828, rdba: 0x01c05fc4(afn 80, blk# 24516) kdk key 8102.2: ncol: 8, len: 43 key: (43): 0c 63 6f 6e 66 69 72 6d 61 74 69 6f 6e 01 30 ff 03 43 53 45 ff 08 4e 65 77 20 59 6f 72 6b 05 32 30 2d 33 30 06 0f c4 29 be 00 00 The key contains all the columns from the index: 0c 63 6f 6e 66 69 72 6d 61 74 69 6f 6e 01 30 ff 03 43 53 45 ff 08 4e 65 77 20 59 6f 72 6b 05 32 30 2d 33 30 06 0f c4 29 be 00 00 —-> last column in this case is the rowid 0x0fc429be slot 0 a) dump the rdba: 0x01c05fc4(afn 80, blk# 24516) alter system dump datafile 80 block 24516; b) check if the above key is in the index block c) if it is not drop and recreate the index d) if the key it is in the index than you will have to check the table using the rowid Causes:==========There are a number of possibilities for corruption: – memory problems cause dropped bits – disk repair utilities zeroing out blocks – system crashes – in 10.2 Bug:7329252 Index corruption after rebuild index ONLINE Fixed-Releases: B200 Details: If there is a heavy concurrency on a table during online index rebuild, the index can be corrupt missing some keys. The next errors can be produced by sql statements: ORA-8102 by a delete/update ORA-1499 by “analyze table validate structure cascade” ORA-600 [kdsgrp1] ORA-600 [qertbFetchByRowID] REFERENCES

Speak Your Mind

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569