ORA-07445錯誤分析

ORA-07445錯誤分析[@more@]

ORA-07445錯誤1:

ORA-07445: exception encountered: core dump

ORA-07445錯誤1->解決思路1

oracle的BUG，在初始化檔案裡面設定引數
event = "10262 trace name context forever, level 1024000"
重啟.

ORA-07445錯誤2:

ORA-07445: exception encountered: core dump [kkojnp()+15097] [SIGSEGV] [Address not mapped to object] [0x2] [] []

ORA-07445錯誤2->解絕思路2

解絕思路2->作者:xzh2000

含row_number()的SQL產生03113/07445錯誤解決2法

今天業務系統突然報了很多錯,發現全是一個SQL產生的,這個SQL中
含有row_number() over.....分析函式,該SQL及錯誤如下：

ORA-03113: end-of-file on communication channel

select * from (select a.PRICE_LEVEL,a.RESELLER_ID,a.USER_NAME,a.RESELLER_NAME,
a.is_confirmed,b.REST_OF_MONEY,b.SALE_OF_MONEY,b.SAVE_OF_MONEY,a.AGENT_USER_ID,
d.CITY_AGENT_BASE_AMOUNT,d.CITY_AGENT_DIFF_PRICE,d.CITY_AGENT_SELL_PROFIT,
row_number() over(ORDER BY a.RESELLER_ID asc) as rn
from AGENT_ACCOUNT c,RESELLER_BASE a,RESELLER_ACCOUNT b,RESELLER_COMPUTE d
where a.RESELLER_ID=b.RESELLER_ID and a.RESELLER_ID = d.RESELLER_ID and a.AGENT_ID=c.AGENT_ID
and a.IS_DELETED='N' and a.AGENT_ID=53981 ) where rn>0 and rn<=20

馬上查udump目錄,發現了一大堆trc的檔案.....摘錄如下：
*** 2004-09-13 09:21:34.948
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [kkojnp()+15097] [SIGSEGV] [Address not mapped to object] [0x2] [] []
Current SQL statement for this session:
select * from (select a.PRICE_LEVEL,a.RESELLER_ID,a.USER_NAME,a.RESELLER_NAME,a.is_confirmed,
b.REST_OF_MONEY,b.SALE_OF_MONEY,b.SAVE_OF_MONEY,a.AGENT_USER_ID,d.CITY_AGENT_BASE_AMOUNT,
d.CITY_AGENT_DIFF_PRICE,d.CITY_AGENT_SELL_PROFIT  ,row_number() over(order by a.REGISTER_TIME  asc) as rn
from RESELLER_BASE a,RESELLER_ACCOUNT b,AGENT_ACCOUNT c,RESELLER_COMPUTE d
where a.RESELLER_ID=b.RESELLER_ID and a.RESELLER_ID = d.RESELLER_ID and a.AGENT_ID=c.AGENT_ID
and a.AGENT_ID=415 and a.IS_DELETED='N' and a.USER_NAME like 'zjjclty%' and a.AGENT_USER_ID=52331
and a.REGISTER_TIME between to_date('2003-9-13 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and to_date('2004-9-13 23:59:59', 'YYYY-MM-DD HH24:MI:SS')) where rn>0 and rn<=35
----- Call Stack Trace -----

在系統從9.2.0.1升級到9.2.0.4時,也曾發現過03113/07445的錯誤,而且
當時也是由row_number() over....引起的,後來將SQL中的row_number()
over(ORDER BY a.RESELLER_ID asc)中的order by a.reseller_id
改為order by 2後,該SQL就正常工作.....
這次的SQL與上次的不同,偶昭樣將欄位改為欄位編號後,發現SQL也不報錯啦,
但結果集卻不再按偶以前要求的排序來顯示。。。。。。

在google與metalink上搜03113&07445&row_number(),找不到解決辦法,
然後只有自己償試解決啦:
分析表/分析索引,錯誤依舊.....
重啟資料庫,問題依舊.......
改寫sql,用rank() over......錯誤依舊.....
改寫sql,新增/*+ leading(a) */,問題解決......

思路3：終極解決辦法！

升級資料庫！升到最高版本(或者在各大論壇上談到ｂｕｇ少一點的版本)！可惡的BUG！