ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORA-07445错误分析

ORA-07445错误分析

原创 Linux操作系统 作者:syzxlyx_cu 时间:2009-09-28 11:12:11 0 删除 编辑

ORA-07445错误分析
===========================================================
作者: xinxinhg(http://xinxinhg.itpub.net)
发表于: 2005.01.11 21:16
分类: Ora-错误分析
出处: http://xinxinhg.itpub.net/post/587/13606
---------------------------------------------------------------

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少一点的版本)!可恶的BUG!

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/20542911/viewspace-615719/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 建立扩展统计
下一篇: ORA-600 [ttcgcshnd-1]
请登录后发表评论 登录
全部评论

注册时间:2009-09-28

  • 博文量
    125
  • 访问量
    124063