告警日志里這兩天一直顯示這個(gè)錯(cuò)誤：
|
1
2
3
4
5
6
7
8
9
10
|
ORA-00600:internalerrorcode,arguments:[kcblasm_1],[103],[],[],[],[],[],[]TueAug1209:20:17CST2014Errorsinfile/u01/app/oracle/admin/orcl/udump/orcl_ora_29974.trc:ORA-00600:internalerrorcode,arguments:[kcblasm_1],[103],[],[],[],[],[],[]TueAug1209:30:17CST2014Errorsinfile/u01/app/oracle/admin/orcl/udump/orcl_ora_30084.trc:ORA-00600:internalerrorcode,arguments:[kcblasm_1],[103],[],[],[],[],[],[]TueAug1209:40:17CST2014Errorsinfile/u01/app/oracle/admin/orcl/udump/orcl_ora_29919.trc:ORA-00600:internalerrorcode,arguments:[kcblasm_1],[103],[],[],[],[],[],[] |
網(wǎng)上查的解決辦法：
1:臨時(shí)的解決方法
如果執(zhí)行計(jì)劃中是hashjoin造成的，在會(huì)話層中設(shè)置"_hash_join_enable"=false，如：altersessionset"_hash_join_enabled"=false亦可;
如果執(zhí)行計(jì)劃是hashgroupby造成的，設(shè)置"_gby_hash_aggregation_enabled"=false
2:根本的解決方法
2.1.優(yōu)化sql語句，避免遇到bug；
2.2.升級(jí)
(1)將數(shù)據(jù)庫升級(jí)psu到10.2.0.5.4和11.2可以修正該問題
(2)對(duì)于10.2.0.5.0到10.2.0.5.3的版本，打PATCH7612454來避免改錯(cuò)誤(該補(bǔ)丁替換lib中的kcbl.o文件)。
通過臨時(shí)解決辦法解決問題示例：
追蹤報(bào)警日志里提示的trace文件，找到導(dǎo)致出現(xiàn)此錯(cuò)誤的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,CUSTOMERIDFROMELITE.TABCINDENTALEFTJOINELITE.OBJECTIVEBONA.RELATION_ID=B.OBJECTIVE_GUIDLEFTJOINELITE.CUSTOMERCONA.CUSTOMER_GUID=C.CUSTOMER_GUIDWHERE(INDENTDATEBETWEEN:1AND:2ORB.MODIFIEDDATEBETWEEN:3AND:4); |
將變量:1,:2,:3,:4替換成具體的值執(zhí)行：
|
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,CUSTOMERIDFROMELITE.TABCINDENTALEFTJOINELITE.OBJECTIVEBONA.RELATION_ID=B.OBJECTIVE_GUIDLEFTJOINELITE.CUSTOMERCONA.CUSTOMER_GUID=C.CUSTOMER_GUIDWHERE(INDENTDATEBETWEEN'2012-06-19'AND'2012-08-19'ORB.MODIFIEDDATEBETWEEN'2012-06-19'AND'2012-08-1'); |
執(zhí)行報(bào)錯(cuò)：
解決辦法：
|
1
|
altersessionset"_hash_join_enabled"=false; |
|
1
|
altersessionset"_gby_hash_aggregation_enabled"=false |
--先嘗試一種，如果一種解決了，就沒必要設(shè)置另外一種了。
然后再次執(zhí)行上面的查詢語句，不報(bào)錯(cuò)啦，嘎嘎
成功啦，(*^__^*)嘻嘻……
讓開發(fā)人員在程序里加上這條命令即可。
