Ошибка index key not found

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)

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';

Then we use these statements to rebuild the indexes.

SQL> ALTER INDEX ERPAPP.FINNS5S1 REBUILD;

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);

Then execute the above statements.

So i have a table with an index called IDX_ATS_CALC_END_TIME. The column is a timestamp value. This column also has a trigger that automatically populates the column when another column (Interval_duration) is populated or updated.

The trigger is below:

TRIGGER "DATAMART"."TRG_ATS_CALC_END_TIME" 
    BEFORE INSERT OR UPDATE OF INTERVAL_DURATION ON DATAMART.AGG_TIME_SUMMARY
    FOR EACH ROW
    DECLARE
BEGIN
IF :New.INTERVAL_DURATION > 0 THEN
   :New.calc_end_time := :New.start_date_time  + pb_util.secondtointerval(:New.INTERVAL_DURATION);
ELSE
:NEW.CALC_END_TIME := :New.start_date_time;
END IF;

    EXCEPTION
    WHEN OTHERS THEN
      pb_util.logdata(1, 'TRG_ATS_CALC_END_TIME', 'Exception Thrown in interval:  ' || :New.Interval_DURATION, SQLERRM  || ' stack: ' || dbms_utility.format_error_backtrace);

END TRG_ATS_CALC_END_TIME;

When my table is initially populated there are no problems. My problem is that when i go to perform an insert/update on the table and try to modify this column by either directly changing the column or just updating the interval_duration column i have this error thrown:

ORA-08102: index key not found, obj# 97523, file 4, block 244 (2)

The index mentioned is a functioned based index. The function being used on the index is a sys_extract_utc on the calc_end_time column.

I’ve spent several days trying to solve this issue. I’ve rebuilt the index, i have tried deleting and recreating the index. These two seem to be the common answer for this problem, but they did not work for me.
I’ve analyzed the index using the following:

ANALYZE INDEX IDX_ATS_CALC_END_TIME VALIDATE STRUCTURE;

and it came back with no problems.

The only time i have been able to successfully update this column without getting this error was by disabling the trigger, performing the update, and then enabling the trigger once again. This is not a viable solution for me.

So i would like to know if anyone has ever encountered this type of problem and what other steps i can try to fix this error.

UPDATE:
below you will find the function pb_util.secondtointerval() code:

FUNCTION SecondToInterval
  (Seconds_IN NUMBER
  )
RETURN CONST.PBInterval
IS
  sec            NUMBER(20, 9);
  days           NUMBER;
  hours          NUMBER;
  minutes        NUMBER;
  seconds        NUMBER(20, 9);
  IntervalAsText NVARCHAR2(32);
  ReturnInterval INTERVAL DAY(9) TO SECOND(9);
begin
  sec     := NVL(Seconds_IN, 0);

  days    := trunc(sec/(24*60*60));
  sec     := sec - days*24*60*60;

  hours   := trunc(sec/(60*60));
  sec     := sec - hours*60*60;

  minutes := trunc(sec/60);
  sec     := sec - minutes*60;

  seconds := trunc(sec);

  sec     := sec - seconds;
  sec     := trunc(1000000000*sec);

  IntervalAsText := cast(days as nvarchar2)
    || ' ' || cast(hours as nvarchar2)
    || ':' || substr('00' || cast(minutes as nvarchar2), -2, 2)
    || ':' || substr('00' || cast(seconds as nvarchar2), -2, 2)
    || '.' || substr('000000000' || cast(sec as nvarchar2), -9, 9);

  --dbms_output.put_line(intervalastext);

  ReturnInterval := TO_DSInterval(IntervalAsText);
  --ReturnInterval := TO_DSInterval('999999999 23:59:59.999999999');
  --dbms_output.put_line(ReturnInterval);

  RETURN ReturnInterval;
EXCEPTION
    WHEN OTHERS THEN
   pb_util.logdata(1, 'PB_UTIL.SecondToInterval', 'ERROR(99A): ', intervalastext);
                dbms_output.put_line(intervalastext);
                RAISE;

end SecondToInterval;

this was written by my predecessor but basically all it does is turn the given numeric value and converts it into an interval value.

Any help or suggestions are greatly appreciated.

Thank you.

In one of our test databases we encountered the following error during an overnight PL/SQL purge job:

ORA-08102: index key not found, obj# 124885, file 48, block 55492 (2) 01-FEB-12

The purpose of the job is to delete data introduced since more than 6 days respecting for that several hierarchical parent-child relationships.  It has been working without any relatives problems since more than 2 years until it started crashing out with the above ORA-08102 error. I aim via this blog article to show how I have resolved this problem.

The first thing I did is of course to identify the corrupted object via the following select:

mhouri.world> ed
    Wrote file afiedt.buf
  1  select substr(object_name,1,30), object_type
  2  from user_objects
  3* where object_id = 154711
;

 SUBSTR(OBJECT_NAME,1,30)       OBJECT_TYPE
------------------------------ ----------------
XXX_ZZZ_UK                        INDEX

The naming standard (_UK) is as such that I immediately identified this index as an automatic index created to enforce a unique constraint. I thought first that I need to rebuild this unique index:

mhouri.world> alter index XXX_ZZZ_UK rebuild;

Index altered.

Unfortunately, when I launched again the purge batch the same error has been raised again. So my second tentative was to drop and recreate the unique constraint and implicitly the unique index.

mhouri.world> alter table t1 drop constraint XXX_ZZZ_UK;

Table altered.

mhouri.world > alter table t1 add constraint XXX_ZZZ_UK unique (id1, id2, dat1);

Table altered.

Of course that you have first to get the DDL definition of the constraint before dropping it so that you will be able to re-created it again.

When the purge process has been re-started following this last intervention, it, unfortunately, raised again the same ORA-08102 on the same object. Finally I have decided to work on the underneath table instead of its unique constraint and unique index.

mhouri.world>> alter table t1 move;

 Command that has invalidated all attached indexes and which obliged me to re-build them:

mhouri.world > select 'alter index ' || index_name || ' rebuild;'
from user_indexes
where status != 'VALID';

Once all indexes rebuilt, I launched again the purge job which surprisingly completed successfully.

Frankly speaking I don’t know if I would have been able to work around this error if I have tried instead of the move command a rebuild or shrink command. But what I have learnt from this experience is that when an index is corrupted it could be repaired by moving table data instead of dropping and recreating the corrupted index as initially indicated by the ORA-08102 error. I have also to admit that I don’t know yet the side effect of the move command and will certainly write something about that if eventually I will come to learn something about that.
.