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

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.

Answers


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.


I think it is happening because your trigger is having a non deterministic function -> pb_util.secondtointerval. I don't really know what this method really does. Try assigning some static value instead to CALC_END_TIME and check if your trigger works.

To support this I am placing a short piece of code here:

SQL> CREATE TABLE t (a INTEGER)
Table created.

SQL> CREATE OR REPLACE FUNCTION f (a INTEGER)
   RETURN INTEGER "DETERMINISTIC"
AS
   cnt   INTEGER;
BEGIN
   RETURN ROUND ("DBMS_RANDOM.VALUE (1, 100)");
END f;
Function created.

SQL> CREATE INDEX t_idx ON t (f(a)) COMPUTE STATISTICS
Index created.

SQL> INSERT INTO t
   SELECT ROWNUM
     FROM user_objects
5 rows created.

SQL> DELETE FROM t
DELETE FROM t
Error at line 28
ORA-08102: index key not found, obj# 48928, file 4, block 36 (2)

Hope it helps !!


Need Your Help

Using PendingIntent OnFinished callback from a Notification Action

android notifications broadcastreceiver android-service android-pendingintent

Actions in Android Notifications require a PendingIntent to operate. Typical examples show an action triggering an app Activity. I'm trying to use a Notification action to open an Android Intent ...

Why not find the assembly

c# asp.net-mvc asp.net-mvc-3 entity-framework-4.1 ef-code-first

I have two project Portfolio.Domain and Portfolio.Web.