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.
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 !!