How to Resolve ORA-08102: index key not found

ORA-08102

Found some ORA-08102 errors during executions of SQL statements like this:


ERROR at line 1:

ORA-08102: index key not found, obj# 98830, file 69, block 120164 (2)



ERROR at line 1:

ORA-08102: index key not found, obj# 98832, file 69, block 120172 (2)



ERROR at line 1:

ORA-08102: index key not found, obj# 98831, file 69, block 120140 (2)

Solutions

index key not found? I suspected that the above indexes has been corrupted logically.

1. Rebuild Indexes

The first solution to ORA-08102 is to rebuild those indexes. First of all, we use those object id to compose rebuild statements like this:


SQL> column stmts format a50;

SQL> select 'ALTER ' || object_type || ' ' || owner || '.' || object_name || ' REBUILD;' stmt from dba_objects where object_id in (98829, 98830, 98831) and object_type = 'INDEX';



STMT

--------------------------------------------------

ALTER INDEX ERPAPP.FINNS5S1 REBUILD;

ALTER INDEX ERPAPP.FINNS6S1 REBUILD;

ALTER INDEX ERPAPP.FINNS7S1 REBUILD;

Then we use these statements to rebuild the indexes.


SQL> ALTER INDEX ERPAPP.FINNS5S1 REBUILD;



Index altered.



SQL> ALTER INDEX ERPAPP.FINNS6S1 REBUILD;



Index altered.



SQL> ALTER INDEX ERPAPP.FINNS7S1 REBUILD;



Index altered.

Some indexes may not be fixed by rebuilding it. You need a stronger treatment.

2. Recreate Indexes

Rebuilding problematic indexes should have solved ORA-08102, but in some cases, you have to use DROP then CREATE INDEX instead.


SQL> select 'DROP INDEX ' || b.owner || '.' || b.index_name || ';' || chr(10) || 'CREATE INDEX ' || b.owner || '.' || b.index_name || ' ON ' || c.table_owner || '.' || c.table_name || ' (' || c.column_name || ') TABLESPACE ' || b.tablespace_name || ';' stmt from dba_objects a inner join dba_indexes b on a.object_type = 'INDEX' and a.owner = b.owner and a.object_name = b.index_name inner join dba_ind_columns c on b.owner = c.index_owner and b.index_name = c.index_name where a.object_id in (98829, 98830, 98831);





STMT

--------------------------------------------------------------------------------

DROP INDEX ERPAPP.FINNS5S1;

CREATE INDEX ERPAPP.FINNS5S1 ON ERPAPP.FINLOC1S025 (SWITCH_SUM_PH) TABLESPACE FINCCORP;



DROP INDEX ERPAPP.FINNS6S1;

CREATE INDEX ERPAPP.FINN65S1 ON ERPAPP.FINLOEC1S026 (SWITCH_SUM_PH) TABLESPACE FINCCORP;



DROP INDEX ERPAPP.FINNS7S1;

CREATE INDEX ERPAPP.FINNS7S1 ON ERPAPP.FINLN1S027 (SWITCH_SUM_PH) TABLESPACE FINCCORP;

Then execute the above statements.

11 thoughts on “How to Resolve ORA-08102: index key not found”

andres January 10, 2020 at 11:15 am


Thanks a lot, you saved my life
1. Ed Chen January 10, 2020 at 2:21 pm
  
  
  It’s my pleasure.
Dai Nghia January 6, 2021 at 3:55 am


thanks! it’s very useful
1. Ed Chen January 6, 2021 at 8:40 am
  
  
  I’m glad it’s helpful.
Ben January 29, 2021 at 2:18 pm


hi,

after drop and build I still get the same error. What could be the problem?

thanks and regards,
Ben
1. Ed Chen January 30, 2021 at 8:44 am
  
  
  Is there any chance that the base table was corrupted?
2. 1. Ben February 3, 2021 at 8:57 pm
    
    
    I will check, thanks for the advise
  2. 1. Ed Chen February 4, 2021 at 1:27 am
      
      
      Anytime.
3. Ayon March 3, 2022 at 7:34 pm
  
  
  Was your issue resolved? if so, what steps you followed. I have the same problem.
Cezar October 9, 2023 at 11:53 am


May be you have a function index and the underlying function is not deterministic.
1. Ed Chen October 9, 2023 at 12:22 pm
  
  
  Interesting, I have never thought about that!