oracle报错（ORA-00600）问题处理

告警日志里这两天一直显示这个错误： ? 1 2 3 4 5 6 7 8 0 Comments

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

?

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

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

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

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

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

本文链接：https://my.lmcjl.com/post/11705.html

展开阅读全文

相关文章

4 评论

留下您的评论.