Skip to Content
Author's profile photo Former Member

Solution for Database error ORA-08102 found in SM21

Hi All,

This document discusses the solution for an error I have encountered while performg a client copy on Oracle database(

I want to share my experience while performing a local client copy.

I was doing a client copy on Unix- Oracle environment and we ran into error “Database error 8102″.  The exact error log from SM21 is below,

Error: (from sm21)

Database error 8102 at DEL access to table ACCTHD

> ORA-08102: index key not found, obj# 31189, file 34, block

> 1200035 (2)

The error clearly shows that index key was not found for an object in the table. Index for the tables mentioned in the error (ACCTHD) in the above case were corrupted. Hence DB action DEL(delete) is failing on these tables. I have checked several forums with the error code ORA-8102 and in most of them suggested to rebuild the index for all tables.

Luckily we have an experienced Oracle DBA in our project and with his help we were able to solve this by rebuilding Index for the table corrupted.

Below are steps to rebuild the Index,

Step  #1 : Logon to your server as ORA<SID>

Step  #2 : Open sqlplus as sysdba

sqlplus /nolog

SQL*Plus: Release

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

SQL> conn /as sysdba


Step #3 : Run the below query on the set of tables to get list if index to be rebuild for the given table

                select index_name, owner from dba_indexes where table_name='<table_name_uppercase>’;

Step #4 : From the list of Index displayed from the above query run the below command to generate the index and wait till you receive the confirmation “Index altered.

SQL> alter index SAPSR3.”ACCTHD~0″ rebuild online parallel 2;

Index altered.

SQL> alter index SAPSR3.”ACCTHD~1″ rebuild online parallel 2;

Index altered.

This solves the issue .

Alternatively we can run the report “RSANAORA” to rebuild the index online/offline. This in turn runs same query in SQL level.

One more best practice is to run the “Update Stats” on the table and index( which were rebuild) so that performance will not be effected due to the rebuild. Hope this page will help to solve your issue when you run into a similar kind of error.

Assigned Tags

      1 Comment
      You must be Logged on to comment or reply to a post.
      Author's profile photo Stefan Koehler
      Stefan Koehler

      Hello Pradeep,

      but your experienced DBA has forgotten one important point - if you rebuild the indexes with parallel clause, this setting is persistent. Maybe you are running parallel DML by using the indexes right now. Here is an example:

      SYS@11G:125> create table test (a number);

      SYS@11G:125> create index i_test on test(a);

      SYS@11G:125> select INDEX_NAME, DEGREE from dba_indexes where index_name = 'I_TEST';

      INDEX_NAME         DEGREE

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

      I_TEST             1

      SYS@11G:125> alter index I_TEST rebuild online parallel 2;

      SYS@11G:125> select INDEX_NAME, DEGREE from dba_indexes where index_name = 'I_TEST';

      INDEX_NAME         DEGREE

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

      I_TEST             2