欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

oracle报错(ORA-00600)问题处理

程序员文章站 2022-06-05 21:13:55
告警日志里这两天一直显示这个错误: ora-00600:internalerrorcode,arguments:[kcblasm_1],[103],[],[],[...

告警日志里这两天一直显示这个错误:

ora-00600:internalerrorcode,arguments:[kcblasm_1],[103],[],[],[],[],[],[]
tueaug1209:20:17cst2014
errorsinfile/u01/app/oracle/admin/orcl/udump/orcl_ora_29974.trc:
ora-00600:internalerrorcode,arguments:[kcblasm_1],[103],[],[],[],[],[],[]
tueaug1209:30:17cst2014
errorsinfile/u01/app/oracle/admin/orcl/udump/orcl_ora_30084.trc:
ora-00600:internalerrorcode,arguments:[kcblasm_1],[103],[],[],[],[],[],[]
tueaug1209:40:17cst2014
errorsinfile/u01/app/oracle/admin/orcl/udump/orcl_ora_29919.trc:
ora-00600:internalerrorcode,arguments:[kcblasm_1],[103],[],[],[],[],[],[]

网上查的解决办法:

1:临时的解决方法
如果执行计划中是hashjoin造成的,在会话层中设置"_hash_join_enable"=false,如:altersessionset"_hash_join_enabled"=false亦可;

如果执行计划是hashgroupby造成的,设置"_gby_hash_aggregation_enabled"=false
2:根本的解决方法
2.1.优化sql语句,避免遇到bug;
2.2.升级
(1)将数据库升级psu到10.2.0.5.4和11.2可以修正该问题
(2)对于10.2.0.5.0到10.2.0.5.3的版本,打patch7612454来避免改错误(该补丁替换lib中的kcbl.o文件)。

通过临时解决办法解决问题示例:

追踪报警日志里提示的trace文件,找到导致出现此错误的sql语句

ora-00600:internalerrorcode,arguments:[kcblasm_1],[103],[],[],[],[],[],[]
currentsqlstatementforthissession:

格式化后的sql语句如下:

selectindentdate,
indentgroup,
transdate,
transby,
transgroup,
feedbackby,
feedbackgroup,
financedate,
financeby,
financegroup,
totalcost,
a.totalpay,
pay_cash,
pay_points,
pay_advance1,
pay_advance2,
pay_type,
trans_pay,
discount_staff,
discount_special,
gain_cash,
gain_points,
gain_advance1,
gain_advance2,
trans_custname,
trans_tel,
trans_province,
trans_city,
trans_address,
trans_zipcode,
trans_weight,
trans_comments,
indent_comments,
indent_id,
a.partner_guid,
a.proxy_guid,
trans_tel2,
cust_media_id,
cust_partner_guid,
cust_proxy_guid,
partner_value,
proxy_value,
cust_partner_value,
cust_proxy_value,
dealby,
a.failreason,
isfoot,
s_reasonid,
dealfailreason,
a.pre_fund,
media_calltype,
pre_advance,
web_flag,
need_invoice,
invoice_title,
trans_area,
ordertype,
pay_pointsprice,
a.media,
userdefinedstatus,
customername,
customerid
fromelite.tabcindenta
leftjoinelite.objectiveb
ona.relation_id=b.objective_guid
leftjoinelite.customerc
ona.customer_guid=c.customer_guid
where(indentdatebetween:1and:2orb.modifieddatebetween:3and:4);

将变量:1,:2,:3,:4替换成具体的值执行:

selectindentdate,
indentgroup,
transdate,
transby,
transgroup,
feedbackby,
feedbackgroup,
financedate,
financeby,
financegroup,
totalcost,
a.totalpay,
pay_cash,
pay_points,
pay_advance1,
pay_advance2,
pay_type,
trans_pay,
discount_staff,
discount_special,
gain_cash,
gain_points,
gain_advance1,
gain_advance2,
trans_custname,
trans_tel,
trans_province,
trans_city,
trans_address,
trans_zipcode,
trans_weight,
trans_comments,
indent_comments,
indent_id,
a.partner_guid,
a.proxy_guid,
trans_tel2,
cust_media_id,
cust_partner_guid,
cust_proxy_guid,
partner_value,
proxy_value,
cust_partner_value,
cust_proxy_value,
dealby,
a.failreason,
isfoot,
s_reasonid,
dealfailreason,
a.pre_fund,
media_calltype,
pre_advance,
web_flag,
need_invoice,
invoice_title,
trans_area,
ordertype,
pay_pointsprice,
a.media,
userdefinedstatus,
customername,
customerid
fromelite.tabcindenta
leftjoinelite.objectiveb
ona.relation_id=b.objective_guid
leftjoinelite.customerc
ona.customer_guid=c.customer_guid
where(indentdatebetween'2012-06-19'and'2012-08-19'orb.modifieddatebetween'2012-06-19'and'2012-08-1');

执行报错:

oracle报错(ORA-00600)问题处理

解决办法:

altersessionset"_hash_join_enabled"=false;

oracle报错(ORA-00600)问题处理

altersessionset"_gby_hash_aggregation_enabled"=false

--先尝试一种,如果一种解决了,就没必要设置另外一种了。

然后再次执行上面的查询语句,不报错啦,嘎嘎

oracle报错(ORA-00600)问题处理

成功啦,(*^__^*)嘻嘻……

让开发人员在程序里加上这条命令即可。