Insert Statement Returns Error ORA-14400 When Parallel Dml Is Enabled (Doc ID 1545131.1)

Last updated on MARCH 27, 2014

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Information in this document applies to any platform.

Symptoms

On production database 11.2.0.3 version:

When attempting to insert in parallel

ALTER SESSION ENABLE PARALLEL DML;

INSERT
/*+ APPEND PARALLEL ("MV_PAT_TEST", 4) */
INTO
  "MV_PAT_TEST" "MV_PAT_TEST"
  ("A",
  "PAT_ID",
  "PAT_KEY",
  "LZP_KEY",
  "B_KEY_1",
  "B_KEY_2",
  "BEH_ART_1",
  "BEH_ART_2")
  (SELECT
/*+ PARALLEL ("TMP_TEST_1", 4) PARALLEL ("TMP_TEST_2", 4) */
/* INPUT_PARAMETER.OUTGRP1 */
  20091/* ATTRIBUTE INPUT_PARAMETER.OUTGRP1.P_A */ "P_A",
  "SORTER"."PAT_ID_1$1" "PAT_ID_1",
  "SORTER"."PAT_KEY_1$1" "PAT_KEY_1",
  "SORTER"."LZP_KEY_1$1" "LZP_KEY_1",
  "SORTER"."B_KEY_1$1" "BSNR_KEY_1",
  "SORTER"."B_KEY_2$1" "BSNR_KEY_2",
  "SORTER"."BEH_ART_1$1" "BEH_ART_1",
  "SORTER"."BEH_ART_2$1" "BEH_ART_2"
FROM
  (SELECT
/*+ PARALLEL ("TMP_TEST_1", 4) PARALLEL ("TMP_TEST_2", 4) */
/* JOINER.OUTGRP1 */
  "TMP_TEST_1"."PAT_ID" "PAT_ID_1$1",
  "TMP_TEST_1"."B_KEY" "B_KEY_1$1",
  "TMP_TEST_1"."BEH_ART" "BEH_ART_1$1",
  "TMP_TEST_2"."B_KEY" "B_KEY_2$1",
  "TMP_TEST_2"."BEH_ART" "BEH_ART_2$1",
  "TMP_TEST_1"."PAT_KEY" "PAT_KEY_1$1",
  "TMP_TEST_1"."LZP_KEY" "LZP_KEY_1$1"
FROM
  "TMP_TEST" "TMP_TEST_1",
"TMP_TEST" "TMP_TEST_2"
  WHERE
  ( "TMP_TEST_1"."PAT_ID" = "TMP_TEST_2"."PAT_ID" ) AND
  ( "TMP_TEST_1"."PAT_KEY" = "TMP_TEST_2"."PAT_KEY" ) AND
  ( "TMP_TEST_1"."B_KEY" < "TMP_TEST_2"."B_KEY" ) AND
  ( "TMP_TEST_1"."LZP_KEY" = "TMP_TEST_2"."LZP_KEY"/* OPERATOR JOINER JOIN CONDITION */ )
ORDER BY
  "TMP_TEST_1"."B_KEY" ASC, "TMP_TEST_2"."B_KEY" ASC, "TMP_TEST_1"."LZP_KEY" ASC/* OPERATOR SORTER: ORDER BY CLAUSE */) "SORTER"
  )
;

the following error occurs.

ERROR
-----------------------
INSERT
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P003
ORA-14400: inserted partition key does not map to any partition

The issue is not seen when the same statement is executed using

 ALTER SESSION disable PARALLEL DML;

 

  Problematic Plan:
  ------------------
 
  ---------------------------------------------------------------------------
  | Id  | Operation                     | Name     |  TQ  |IN-OUT| PQ Distrib
  ---------------------------------------------------------------------------
  |   0 | INSERT STATEMENT              |          |      |      |
  |   1 |  PX COORDINATOR               |          |      |      |
  |   2 |   PX SEND QC (RANDOM)         | :TQ10001 |Q1,01 | P->S | QC (RAND)
  |   3 |    LOAD AS SELECT             | T1       |Q1,01 | PCWP |
  |   4 |     VIEW                      |          |Q1,01 | PCWP |
  |   5 |      SORT ORDER BY            |          |Q1,01 | PCWP |
  |   6 |       PX RECEIVE              |          |Q1,01 | PCWP |
  |   7 |        PX SEND PARTITION (KEY)| :TQ10000 |Q1,00 | P->P | PART (KEY)
  |   8 |         PX BLOCK ITERATOR     |          |Q1,00 | PCWC |
  |   9 |          TABLE ACCESS FULL    | T2       |Q1,00 | PCWP |
  ---------------------------------------------------------------------------
 
  Column Projection Information (identified by operation id):
  -----------------------------------------------------------
 
   1 - SYSDEF[4], SYSDEF[32720], SYSDEF[1], SYSDEF[112], SYSDEF[32720]
   2 - (#keys=0) SYSDEF[4], SYSDEF[32720], SYSDEF[1], SYSDEF[112],  SYSDEF[32720]
   3 - SYSDEF[4], SYSDEF[32720], SYSDEF[1], SYSDEF[112], SYSDEF[32720]
   5 - (#keys=1) "T2_1"."B_KEY"[NUMBER,22], "PAT_ID"[NUMBER,22]
   6 - "T2_1"."B_KEY"[NUMBER,22], "PAT_ID"[NUMBER,22]
   7 - (#keys=1) "T2_1"."B_KEY"[NUMBER,22], "PAT_ID"[NUMBER,22]  
   8 - "PAT_ID"[NUMBER,22], "T2_1"."BSNR_KEY"[NUMBER,22]
   9 - "PAT_ID"[NUMBER,22], "T2_1"."BSNR_KEY"[NUMBER,22]


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms