ORA-14400: inserted partition key does not map to any partition

The ORA-14400: inserted partition key does not map to any partition error in Oracle comes when you try to insert value in a partition table which does not fit in any defined partition for the table.

ORA-14400 Error

As per Oracle Error Message Guide,

Cause: An attempt was made to insert a record into, a Range or Composite Range object, with a concatenated partition key that is beyond the concatenated partition bound list of the last partition -OR- An attempt was made to insert a record into a List object with a partition key that did not match the literal values specified for any of the partitions.
Action:Do not insert the key. Or, add a partition capable of accepting the key, Or add values matching the key to a partition specification

Let’s assume, there is list partition table xx_list_partition with 2 partitions on column batch_id.

xx_part_1 value 1
xx_part_2 value 2

Now you are inserting data in xx_list_partition with batch_id = 3.

Now, Oracle Database is not able to find where this value/record should fit and you get ORA-14400: inserted partition key does not map to any partition error. In other words, there is no partition defined which can hold the value with batch_id = 3.

Let’s reproduce the error,

Create a list partition table xx_list_partition as shown below.

CREATE TABLE xx_list_partition (
    value_1   NUMBER,
    value_2   NUMBER,
    batch_id   NUMBER
)
    PARTITION BY LIST ( batch_id ) 
    ( PARTITION xx_batch_1 VALUES ( 1 ),
      PARTITION xx_batch_2 VALUES ( 2 )
    );

Now let’s insert a record with batch_id = 1.

insert into xx_list_partition values (1,2,1);  
1 row inserted.

Insert is successful and 1 row inserted.

Insert record with batch_id = 3

insert into xx_list_partition values (1,2,3); 
ORA-14400: inserted partition key does not map to any partition

You can see, insert is not successful and ended with ORA-14400 error.

ORA-14400 Solution

Make sure to insert the values which satisfy the partition or make sure you create a partition table always with the default clause to avoid ORA-14400: inserted partition key does not map to any partition error in Oracle Database.

CREATE TABLE xx_list_partition (
    value_1   NUMBER,
    value_2   NUMBER,
    batch_id   NUMBER
)
    PARTITION BY LIST ( batch_id ) 
    ( PARTITION xx_batch_1 VALUES ( 1 ),
      PARTITION xx_batch_2 VALUES ( 2 ),
      PARTITION xx_batch_default VALUES (DEFAULT)
    );
insert into xx_list_partition values (1,2,1);  

insert into   xx_list_partition values (1,2,3);

Insert is successful and data is inserted in the table. Oracle database maps batch_id = 3 records to default partition.

I hope you liked this article. Please share and subscribe.