This error was noticed in a Development database when the Developers tried to delete a row from a table called LIMITS:
SQL> DELETE FROM LIMITS WHERE ACCT_ID = 1496605012;
ORA-08102: index key not found, obj# 7941, file 11, block 398468 (2)
At the first glance, it was clear that this could be due to Index Corruption. It basically means that the stored key in the index are different from the value in the table.
Solution: Drop the index and re-create it.
Find out what Index is facing this Issue:
SQL> select owner,OBJECT_NAME,OBJECT_TYPE,OBJECT_ID from dba_objects where OBJECT_ID = 7941;
OWNER
-------------
OBJECT_NAME
-------------------
OBJECT_TYPE OBJECT_ID
------------------- ----------
CCRPROD
IND12LIMITS
INDEX 7941
Generate the Index Creation DDL Statement:
SQL> set pages 999;
SQL> set long 90000;
SQL> select dbms_metadata.get_ddl('INDEX','IND12LIMITS','CCRPROD') from dual;
DBMS_METADATA.GET_DDL('INDEX','IND12LIMITS','CCRPROD')
--------------------------------------------------------------------------
CREATE INDEX "CCRPROD"."IND12LIMITS" ON "CCRPROD"."LIMITS" ("PARTY_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "CCR_INDEX_TS"
Drop the index:
SQL> DROP INDEX CCRPROD.IND12LIMITS;
Create the Index using the DDL statement generated.
Note: Many blogs would tell you to rebuild the index. But how can rebuilding the index fix this issue? Rebuilding indexes generally reads itself to generate the new rebuilt index. To fix this issue, the index needs to read the values from the table directly which can be done only when creating a new index.
