ORA-00600: internal error code, arguments: [kkpamDGSPam1]

SQL> select * from v$version where rownum=1;

BANNER
----------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

TodayI met ORA-600 while creating table.

Error: Creation of stage table OPT_ACTVY_DATA_DUMP_SFCT failed with error - ORA-00600: internal error code, arguments: [kkpamDGSPam1], [], [], [], [], [], [], [], [], [], [], []

Oracle Error Occured.

Error Code: -20003

SQL code as below:

create table OPT_ACTVY_DATA_DUMP_SFCT

parallel 2 TABLESPACE OPTIMA01M

nologging as SELECT

BUS_UNIT_NAME,

prmtn_start_date_fy,

PRMTN_ID,

ACTVY_LONG_NAME,

PRMTN_NAME,

ACTVY_PYMT_METHD_CODE,

ACTVY_SBL_ID,

INTRN_ORDR_CODE,

ACTVY_TYPE_CODE,

ACTVY_SUB_TYPE,

COST_ELEM_CODE,

PGM_START_DATE,

PGM_END_DATE,

PRMTN_PERD_START_DATE,

PRMTN_PERD_END_DATE,

ACTVY_STOP_DATE,

STTUS_CODE,

GPPDB_INTFC_IND,

GPPDB_INTFC_DATE,

FUND_ID,

INVC_COND_TYPE_CODE,

PRDCT_FIXED_COST_AMT,

VAR_COST_ESTMT_AMT,

CALC_INDEX_NUM,

ACTL_VAR_COST_NUM,

REVSD_VAR_ESTMT_COST_AMT,

MANUL_COST_OVRRD_AMT,

ESTMT_COST_IND,

ACCRL_ESTMT_COST_AMT,

ACCRL_ACTL_COST_AMT,

ANTCP_PYMT_AMT,

CMMNT_DESC,

ACCRL_ESTMT_COST_CMMNT

from

OPT_ACTVY_DATA_DUMP_VW;

tried to explain plan for :

SQL> EXPLAIN PLAN FOR create table robinson
2 parallel 2 TABLESPACE OPTIMA01M
3 nologging as SELECT
4 BUS_UNIT_NAME,
5 prmtn_start_date_fy,
6 PRMTN_ID,
7 ACTVY_LONG_NAME,
8 PRMTN_NAME,
9 ACTVY_PYMT_METHD_CODE,
10 ACTVY_SBL_ID,
11 INTRN_ORDR_CODE,
12 ACTVY_TYPE_CODE,
13 ACTVY_SUB_TYPE,
14 COST_ELEM_CODE,
15 PGM_START_DATE,
16 PGM_END_DATE,
17 PRMTN_PERD_START_DATE,
18 PRMTN_PERD_END_DATE,
19 ACTVY_STOP_DATE,
20 STTUS_CODE,
21 GPPDB_INTFC_IND,
22 GPPDB_INTFC_DATE,
23 FUND_ID,
24 INVC_COND_TYPE_CODE,
25 PRDCT_FIXED_COST_AMT,
26 VAR_COST_ESTMT_AMT,
27 CALC_INDEX_NUM,
28 ACTL_VAR_COST_NUM,
29 REVSD_VAR_ESTMT_COST_AMT,
30 MANUL_COST_OVRRD_AMT,
31 ESTMT_COST_IND,
32 ACCRL_ESTMT_COST_AMT,
33 ACCRL_ACTL_COST_AMT,
34 ANTCP_PYMT_AMT,
35 CMMNT_DESC,
36 ACCRL_ESTMT_COST_CMMNT
37 from
38 OPT_ACTVY_DATA_DUMP_VW;
OPT_ACTVY_DATA_DUMP_VW
*
ERROR at line 38:
ORA-00600: internal error code, arguments: [kkpamDGSPam1], [], [], [], [], [], [], [], [], [], [], []

only explain plan for select part:

SQL> explain plan for SELECT

2 BUS_UNIT_NAME,

3 prmtn_start_date_fy,

4 PRMTN_ID,

5 ACTVY_LONG_NAME,

6 PRMTN_NAME,

7 ACTVY_PYMT_METHD_CODE,

8 ACTVY_SBL_ID,

9 INTRN_ORDR_CODE,

10 ACTVY_TYPE_CODE,

11 ACTVY_SUB_TYPE,

12 COST_ELEM_CODE,

13 PGM_START_DATE,

14 PGM_END_DATE,

15 PRMTN_PERD_START_DATE,

16 PRMTN_PERD_END_DATE,

17 ACTVY_STOP_DATE,

18 STTUS_CODE,

19 GPPDB_INTFC_IND,

20 GPPDB_INTFC_DATE,

21 FUND_ID,

22 INVC_COND_TYPE_CODE,

23 PRDCT_FIXED_COST_AMT,

24 VAR_COST_ESTMT_AMT,

25 CALC_INDEX_NUM,

26 ACTL_VAR_COST_NUM,

27 REVSD_VAR_ESTMT_COST_AMT,

28 MANUL_COST_OVRRD_AMT,

29 ESTMT_COST_IND,

30 ACCRL_ESTMT_COST_AMT,

31 ACCRL_ACTL_COST_AMT,

32 ANTCP_PYMT_AMT,

33 CMMNT_DESC,

34 ACCRL_ESTMT_COST_CMMNT

35 from

36 OPT_ACTVY_DATA_DUMP_VW a;

Explained.

ORA-600 [Kkpamdgspam1] When Joining Partitioned Tables at Different Partitioning Levels [ID 790630.1]

Cause

The erroris caused by unpublished Bug 7182839 "ORA-600 WHEN EXPLAIN PLAN FOR HASH JOIN SQL," which affects only 11.1.0.6 and 11.1.0.7.

The call stack may look like this:

kkpamDGSPam qertqosSetPart qertqosSetPropertie qertqoAllocate qknRwsAllocateTree

The error occurs when all of the following are true:

1. Two partitioned tables are joined at different levels (one at partition level and the other at the subpartition level).
2. The optimizer picks a plan with slave mapping based on both left and right sides of the join.
3. The hash join order of the two partitioned tables is swapped.

Solution

1. Upgrade to 11.2, when available for you platform.
2. Apply Patch 7182839 on top of 11.1.0.6 or 11.1.0.7, if available for your platform.
3. Use the workaround of adding the ORDERED hint to the query, as shown in this example:

select /*+ ordered */ a.* from res_passenger a, res_transaction b where a.pnr_nbr = b.pnr_nbr;

Metalink give us the solution : add hint ordered, but here we can’t add such hint, because it would lead CBO to choose a very bad execution plan.

So I add two hint /*+ leading(OPT_PRMTN_FDIM) use_hash(OPT_PRMTN_FDIM,OPT_ACTVY_FCT) */ to fix it .

SQL> create table robinson
2 parallel 2 TABLESPACE OPTIMA01M
3 nologging as SELECT /*+ leading(OPT_PRMTN_FDIM) use_hash(OPT_PRMTN_FDIM,OPT_ACTVY_FCT) */
4 BUS_UNIT_NAME,
5 prmtn_start_date_fy,
6 PRMTN_ID,
7 ACTVY_LONG_NAME,
8 PRMTN_NAME,
9 ACTVY_PYMT_METHD_CODE,
10 ACTVY_SBL_ID,
11 INTRN_ORDR_CODE,
12 ACTVY_TYPE_CODE,
13 ACTVY_SUB_TYPE,
14 COST_ELEM_CODE,
15 PGM_START_DATE,
16 PGM_END_DATE,
17 PRMTN_PERD_START_DATE,
18 PRMTN_PERD_END_DATE,
19 ACTVY_STOP_DATE,
20 STTUS_CODE,
21 GPPDB_INTFC_IND,
22 GPPDB_INTFC_DATE,
23 FUND_ID,
24 INVC_COND_TYPE_CODE,
25 PRDCT_FIXED_COST_AMT,
26 VAR_COST_ESTMT_AMT,
27 CALC_INDEX_NUM,
28 ACTL_VAR_COST_NUM,
29 REVSD_VAR_ESTMT_COST_AMT,
30 MANUL_COST_OVRRD_AMT,
31 ESTMT_COST_IND,
32 ACCRL_ESTMT_COST_AMT,
33 ACCRL_ACTL_COST_AMT,
34 ANTCP_PYMT_AMT,
35 CMMNT_DESC,
36 ACCRL_ESTMT_COST_CMMNT
37 from
38 OPT_ACTVY_DATA_DUMP_VW;

Table created.

Elapsed: 00:00:40.53