客戶反應無法在生産環境建立物化視圖,登入遠端上去嘗試建立後發現報錯下面錯誤:
ORA-00604: error occurred at recursive SQL level 1
ORA-08102: index key not found, obj# 399205, file 36, block 200745 (2)
接下來看下8102是什麼錯誤:
[oracle@rac01 ~]$ oerr ora 8102
08102, 00000, "index key not found, obj# %s, file %s, block %s (%s)"
// *Cause: Internal error: possible inconsistency in index
// *Action: Send trace file to your customer support representative, along
// with information on reproducing the error
并沒有明确的解釋8102的錯誤,這下隻能從mos上擷取資訊了。在mos上搜尋到了一篇文章8102.1:
OERR: ORA-8102 "index key not found, obj# %s, file %s, block %s (%s)" (Doc ID 8102.1)
錯誤描述:
ORA-8102的原因是表或者LOB segment存儲的鍵值和索引不一緻造成的,通過出錯誤中提示的obj#,可以在dba_objects中定位到對象,例如:
select * from dba_objects where object_id = 46115
如果在表和索引真的存在鍵值不一緻,那麼下面的分析表指令會報錯ORA-1499
analyze table table_name validate structure cascade;
一種非主流的方法可以去查詢出不一緻的鍵值,index name是在dba_objects中查詢出的索引
select /+ FULL(t1) / indexed_column_list
from table_name t1
minus
select /+ index(t ) / indexed_column_list
from table_name t;
引起ORA-8102的原因有以下幾點:
1.Oracle defect:由bug引起
2.Block corruption in the index or in the table:索引或表的壞塊
定位索引壞塊
analyze index index_name validate structure;
定位表壞塊
analyze table table_name validate structure;
3.Hardware / IO
4.Function-based indexes:函數索引
解決方案:
解決方案很簡單,重建索引即可:
alter index index_name rebuild online;