oracle 7612454补丁,ORA-00600 内部错误代码, 参数 [kcblasm_1], [103], [], [], [], [], [], []

开发人员提出应用程序执行sql语句时报出如下错误：

java.sql.SQLException: ORA-00600: 内部错误代码, 参数: [kcblasm_1], [103], [], [], [], [], [], []；

相应的sql语句如下：

SELECT jcenquiry0_.*

FROM TBL_JC_JCENQUIRY jcenquiry0_,

TBL_JC_JCCUSTOMER jccustomer1_,

TBL_JCCONTACT jccontact2_

WHERE (jcenquiry0_.customer_no = jccustomer1_.customer_no(+))

AND (jcenquiry0_.contact_no = jccontact2_.contact_no(+))

AND (jcenquiry0_.enquiry_from = '9')

ORDER BY jcenquiry0_.create_time DESC

这个sql语句是一个多表关联，而这个多表关联执行计划是hash join的关联方式，在mos上面找到一篇文章

ORA-600 [kcblasm_1] In 10.2.0.5. (Doc ID 1133845.1)

CHANGES

This problem is hit only in 10.2.0.5, up to PSU 10.2.0.5.4 in which problem is fixed.

Problem is affecting 10.2.0.5 to 10.2.0.5.3.

CAUSE

This is is a known and common problem hit in 10.2.0.5, investigated and corrected in unpublished Bug 7612454.

The problem was introduced in 10.2.0.5, by the fix of Bug:7523755.

This has been corrected in PSU 10.2.0.5.4 and 11.2.

SOLUTION

To quickest way to the solution of this problem is to apply PSU 10.2.0.5.4, Patch 12419392 or later.

All alternative solutions for this problem are listed below:

• Upgrade the database to 11.2.

OR

• Apply 10.2.0.5.4 Patch Set Update (Patch 12419392) or later PSUs where bug is fixed.

The available PSUs are mentioned in "10.2.0.5 Patch Set Updates - List of Fixes in each PSU" (Document 1337394.1)

OR

• Apply interim Patch 7612454 on top of 10.2.0.5 (10.2.0.5.0-10.2.0.5.3):

o For UNIX / Linux platforms apply Patch 7612454 available for download on MOS.

o For Windows platforms apply Patch 3 or higher.

Please check Document 342443.1 for latest patches available for Windows on top of 10.2.0.5.

Note:

It is possible to hit ORA-600 [kcblasm_1] in 10.2.0.4 or 11.1.0.7 if an early version of the one-off Patch 7523755 was applied.

The problem patch Patch 7523755 was quickly removed and a correct version of the patch is since long available for download at MOS.

For details of the problem of hitting ORA-600 [kcblasm_1] in 10.2.0.4 or 11.1.0.7, please take part of Document 849381.1.

Oracle提出这个是一个bug，在10.2.0.5.4和以后版本已经解决了，当大表hash join关联或者大表执行hash group by就有可能出现上述的ora-600 [kcblasm_1], [103], [], [], [], [], [], []问题。

可以选择规避掉hash join执行计划或者hash group by的执行计划来避开相应sql引起的错误，我们可以在执行这个sql语句之前：

alter session set “_hash_group_enabled”=false 禁掉hash join连接方式，或者sql语句里面使用hint no_use_hash。

alter session set “_gby_hash_aggregation_enabled”=falase 禁掉hash group by执行路径

Oralce给出的最快捷的方式是直接打patch到10.2.0.5.4或者以上版本，也可以选择在linux和unix平台打patch 7612454去解决这个问题，而windows下需要下载patch 3或者以上版本来解决这个问题。

这里有点纳闷的是oracle居然把这个重大的bug放到oracle最后的一个PSR中，这个还是值得我们dba特别关注。

把索引删了。解决了。。。。！！