oracle 获取原因（ORA-8102“未找到索引键”）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10999239/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Reasons for getting (ORA-8102 "index key not found")
提问by James213
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.
所以我有一个索引名为 IDX_ATS_CALC_END_TIME 的表。该列是时间戳值。此列还有一个触发器，可在填充或更新另一列 (Interval_duration) 时自动填充该列。
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:
当我的表最初被填充时，没有问题。我的问题是，当我对表执行插入/更新并尝试通过直接更改列或仅更新 interval_duration 列来修改此列时，我抛出了此错误：
ORA-08102: index key not found, obj# 97523, file 4, block 244 (2)
ORA-08102: 未找到索引键，obj# 97523，文件 4，块 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.
提到的索引是基于函数的索引。索引上使用的函数是 calc_end_time 列上的 sys_extract_utc。
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;
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:
更新：在下面你会找到函数 pb_util.secondtointerval() 代码：
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.
谢谢你。
回答by Bob Jarvis - Reinstate Monica
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.
这将告诉您 Oracle 遇到问题的对象。很可能是您怀疑的索引，但也可能不是。
Share and enjoy.
分享和享受。
回答by Saurabh R S
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.
我认为这是因为您的触发器具有非确定性函数 -> pb_util.secondtointerval。我真的不知道这个方法到底是做什么的。尝试将一些静态值分配给 CALC_END_TIME 并检查您的触发器是否有效。
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 !!
希望能帮助到你 ！！
