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. My 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.

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 FI
NCCORP;

DROP INDEX ERPAPP.FINNS6S1;
CREATE INDEX ERPAPP.FINN65S1 ON ERPAPP.FINLOEC1S026 (SWITCH_SUM_PH) TABLESPACE F
INCCORP;

DROP INDEX ERPAPP.FINNS7S1;
CREATE INDEX ERPAPP.FINNS7S1 ON ERPAPP.FINLN1S027 (SWITCH_SUM_PH) TABLESPACE FIN
CCORP;

Then execute those statements.

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

Leave a Reply

Your email address will not be published. Required fields are marked *