Reasons for getting (ORA-8102 "index key not found")

Question

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.

Answer 1

Try the following:

SELECT *
  FROM ALL_OBJECTS
  WHERE OBJECT_ID = 97523

This will tell you the object Oracle is having problems with. Most likely it's the index you suspect, but perhaps not.

Share and enjoy.