oracle报错（ORA-00600）问题处理

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

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');

执行报错：

解决办法：

altersessionset"_hash_join_enabled"=false;

altersessionset"_gby_hash_aggregation_enabled"=false

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

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

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

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