首页 > 代码库 > ORA-00600: internal error code, arguments: [kcblasm_1], [103], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [kcblasm_1], [103], [], [], [], [], [], []

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

ORA-00600: internal error code, arguments: [kcblasm_1], [103], [], [], [], [], [], []

Tue Aug 12 09:20:17 CST 2014

Errors in file /u01/app/oracle/admin/orcl/udump/orcl_ora_29974.trc:

ORA-00600: internal error code, arguments: [kcblasm_1], [103], [], [], [], [], [], []

Tue Aug 12 09:30:17 CST 2014

Errors in file /u01/app/oracle/admin/orcl/udump/orcl_ora_30084.trc:

ORA-00600: internal error code, arguments: [kcblasm_1], [103], [], [], [], [], [], []

Tue Aug 12 09:40:17 CST 2014

Errors in file /u01/app/oracle/admin/orcl/udump/orcl_ora_29919.trc:

ORA-00600: internal error code, arguments: [kcblasm_1], [103], [], [], [], [], [], []

网上查的解决办法:

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

如果执行计划是hash group by 造成的,设置"_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的版本,打PATCH 7612454来避免改错误(该补丁替换lib中的kcbl.o文件)。 

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

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

ORA-00600: internal error code, arguments: [kcblasm_1], [103], [], [], [], [], [], []

Current SQL statement for this session:

格式化后的sql语句如下:

SELECT INDENTDATE,

       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

  FROM ELITE.TABCINDENT A

  LEFT JOIN ELITE.OBJECTIVE B

    ON A.RELATION_ID = B.OBJECTIVE_GUID

  LEFT JOIN ELITE.CUSTOMER C

    ON A.CUSTOMER_GUID = C.CUSTOMER_GUID

 WHERE (INDENTDATE BETWEEN :1 AND :2 OR B.MODIFIEDDATE BETWEEN :3 AND :4);

 

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

SELECT INDENTDATE,

       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

  FROM ELITE.TABCINDENT A

  LEFT JOIN ELITE.OBJECTIVE B

    ON A.RELATION_ID = B.OBJECTIVE_GUID

  LEFT JOIN ELITE.CUSTOMER C

ON A.CUSTOMER_GUID = C.CUSTOMER_GUID

WHERE (INDENTDATE BETWEEN ‘2012-06-19‘ AND ‘2012-08-19‘ OR B.MODIFIEDDATE BETWEEN ‘2012-06-19‘ AND ‘2012-08-1‘);

执行报错:

 

解决办法:

alter session set "_hash_join_enabled"=false;

 

alter session set  "_gby_hash_aggregation_enabled"=false

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

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

 

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

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