ORA-07445错误分析(oracle9i)

ORA-07445错误分析

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！