ORA-08102: index key not found

ORA-08102: index key not found

Environment:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

SUSE Linux Enterprise Server 10 (x86_64)

Issue :

One of our developers reported that he is getting an error when he was trying to DELETE data from a table.

Error Message:

SQL> delete from purchase;

delete from purchase

            *

ERROR at line 1:

ORA-08102: index key not found, obj# 77136, file 18, block 291 (2)

-- Find out the object referenced in the error message.

SQL> select OWNER,OBJECT_NAME,SUBOBJECT_NAME,CREATED,last_ddl_time from dba_objects  where object_id=77136;

OWNER

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

OBJECT_NAME

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

SUBOBJECT_NAME                 CREATED   LAST_DDL_

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

SNIADM

IX7_PURCHASE

MAR2012_SP2                    27-JUN-12 27-JUN-12

This is an index named IX7_PURCHASE and it’s a partitioned index and sub-partition MAR2012_SP2 is what is causing the issue.

-- I tried to rebuild the index

SQL> alter index SNIADM.IX7_PURCHASE rebuild online;

alter index SNIADM.IX7_PURCHASE rebuild online

                       *

ERROR at line 1:

ORA-14086: a partitioned index may not be rebuilt as a whole

Oops! It’s a partitioned index and hence the rebuild cannot be as a whole. This is what the error message means.

> oerr ora 14086

14086, 00000, "a partitioned index may not be rebuilt as a whole"

// *Cause:  User attempted to rebuild a partitioned index using

//          ALTER INDEX REBUILD statement, which is illegal

// *Action: Rebuild the index a partition at a time (using

//          ALTER INDEX REBUILD PARTITION) or drop and recreate the

//          entire index

-- Then I tried to rebuild the index at the subpartition level where the problem occurred.

SQL> alter index SNIADM.IX7_PURCHASE rebuild subpartition MAR2012_SP2 TABLESPACE SNI_IDX2 online;

Index altered.

The delete from the table failed again with the same error message.

-- Then I tried to rebuild all subpartitions of the index by using the following statement output spooled to a SQL file.

select 'alter index '||index_owner||'.'||index_name||' rebuild subpartition '||subpartition_name||' tablespace '||tablespace_name||' online;' from dba_ind_subpartitions where index_name='IX7_PURCHASE';

The delete from the table failed again with the same error message.

-- Then I dropped and recreated the index. This fixed the issue.

I need to explore if there is a better way or recommended way to fix this issue. If anyone else knows a better way, please suggest.