告警日志里这两天一直显示这个错误：
|
1 2 3 4 5 6 7 8 9 10 |
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语句
|
1 2 |
ORA-00600:internalerrorcode,arguments:[kcblasm_1],[103],[],[],[],[],[],[] CurrentSQLstatementforthissession: |
格式化后的sql语句如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
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替换成具体的值执行：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
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' ); |
执行报错：
解决办法：
|
1 |
altersessionset "_hash_join_enabled" = false ; |
|
1 |
altersessionset "_gby_hash_aggregation_enabled" = false |
--先尝试一种，如果一种解决了，就没必要设置另外一种了。
然后再次执行上面的查询语句，不报错啦，嘎嘎
成功啦，(*^__^*)嘻嘻……
让开发人员在程序里加上这条命令即可。
查看更多关于oracle报错（ORA-00600）问题处理的详细内容...