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.

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

Reproduce ORA-14400 Error and solution

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

Table Script

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 )
    );

 

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.

Now, 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 partition key does not map to any partition error.

Here, you are inserting data in xx_list_partition with batch_id = 3. Oracle Database is not able to find where this value/record should fit and you get partition error. In other words, there is no partition defined which can hold the value with batch_id = 3.

You would get same error while updating as well.

Solution

Make sure to insert/update 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.

Table Script

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);

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

Summary

In conclusion, encountering the ORA-14400 error means that an attempt to insert data into a partitioned table has failed because the partition key value does not align with any existing partition. Addressing this issue involves either modifying the data being inserted to fit into an existing partition or altering the partitioned table to accommodate the new data.

Implementing a comprehensive and flexible partitioning strategy, including possibly a default partition, can mitigate the occurrence of this error, ensuring smooth data insertion processes. Proper planning, testing, and maintenance of partitioned tables are essential to maximize their benefits while minimizing potential errors and data integrity issues.

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