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');
执行报错:
解决办法:
altersessionset"_hash_join_enabled"=false;
altersessionset"_gby_hash_aggregation_enabled"=false
--先尝试一种,如果一种解决了,就没必要设置另外一种了。
然后再次执行上面的查询语句,不报错啦,嘎嘎
成功啦,(*^__^*)嘻嘻……
让开发人员在程序里加上这条命令即可。
推荐阅读
-
Oracle 管道 解决Exp/Imp大量数据处理问题
-
Oracle 管道 解决Exp/Imp大量数据处理问题
-
oracle报错(ORA-00600)问题处理
-
oracle报错ORA-12557:TNS:协议适配器不可加载的问题如何解决?
-
解决Oracle登录时出现无法处理服务名问题
-
Java oracle数据库填数据时报错ora-12505问题解决办法
-
maven添加oracle依赖失败问题的处理方法
-
Oracle利用errorstack追踪tomcat报错ORA-00903 无效表名的问题
-
用户为scott时,用pl/sql登录oracle,报错问题的解决方案
-
win10 oracle11g安装报错问题集合 附解决方法