Ошибка ora 14400 inserted partition key does not map to any partition

select partition_name,column_name,high_value,partition_position
from ALL_TAB_PARTITIONS a , ALL_PART_KEY_COLUMNS b 
where table_name='YOUR_TABLE' and a.table_name = b.name;

This query lists the column name used as key and the allowed values. make sure, you insert the allowed values(high_value). Else, if default partition is defined, it would go there.

EDIT:

I presume, your TABLE DDL would be like this.

CREATE TABLE HE0_DT_INF_INTERFAZ_MES
  (
    COD_PAIS NUMBER,
    FEC_DATA NUMBER,
    INTERFAZ VARCHAR2(100)
  )
  partition BY RANGE(COD_PAIS, FEC_DATA)
  (
    PARTITION PDIA_98_20091023 VALUES LESS THAN (98,20091024)
  );

Which means I had created a partition with multiple columns which holds value less than the composite range (98,20091024);

That is first COD_PAIS <= 98 and Also FEC_DATA < 20091024

Combinations And Result:

98, 20091024     FAIL
98, 20091023     PASS
99, ********     FAIL
97, ********     PASS
 < 98, ********     PASS

So the below INSERT fails with ORA-14400; because (98,20091024) in INSERT is EQUAL to the one in DDL but NOT less than it.

SQL> INSERT INTO HE0_DT_INF_INTERFAZ_MES(COD_PAIS, FEC_DATA, INTERFAZ)
                                  VALUES(98, 20091024, 'CTA');  2
INSERT INTO HE0_DT_INF_INTERFAZ_MES(COD_PAIS, FEC_DATA, INTERFAZ)
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

But, we I attempt (97,20091024), it goes through

SQL> INSERT INTO HE0_DT_INF_INTERFAZ_MES(COD_PAIS, FEC_DATA, INTERFAZ)
  2                                    VALUES(97, 20091024, 'CTA');

1 row created.

Error
While inserting data to a partitioned table, got below error.

SQL> insert into TEST_RANGE values(to_date('24032020','ddmmyyyy'),100);
insert into TEST_RANGE values(to_date('24032020','ddmmyyyy'),100);
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition


Cause
Error due to High partition range is not valid with the value you are using in Insert Operation.

Solution
You need to make a valid partition for inserting the data into the Partition Table

You can check partition of table with its range value with following Query:

SQL> select partition_name,high_value from dba_tab_partitions where table_name='TEST_RANGE';

PARTITION_NAME HIGH_VALUE
-------------- --------------------------------------------------------------------------------
P3             TO_DATE(' 2020-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P2             TO_DATE(' 2020-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P1             TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

For More Detail regarding Parttion table, use following query:

select partition_name,column_name,high_value,partition_position
from ALL_TAB_PARTITIONS a , ALL_PART_KEY_COLUMNS b
where table_name='YOUR_TABLE' and a.table_name = b.name;


It is an older article and it’s assuming the year is 2015.
Change the script so partitions look like this:

PARTITION BY RANGE (created_Date)
(PARTITION tab1_part_2017 VALUES LESS THAN (TO_DATE('01/01/2018', 'DD/MM/YYYY')) TABLESPACE users,
 PARTITION tab1_part_2018 VALUES LESS THAN (TO_DATE('01/01/2019', 'DD/MM/YYYY')) TABLESPACE users);

The whole script should look like this:

DROP TABLE tab1 PURGE;

CREATE TABLE tab1 (
  id                NUMBER,
  code              VARCHAR2(20),
  description       VARCHAR2(50),
  created_date      DATE,
  CONSTRAINT tab1_pk PRIMARY KEY (id)
)
PARTITION BY RANGE (created_Date)
(PARTITION tab1_part_2017 VALUES LESS THAN (TO_DATE('01/01/2018', 'DD/MM/YYYY')) TABLESPACE users,
 PARTITION tab1_part_2018 VALUES LESS THAN (TO_DATE('01/01/2019', 'DD/MM/YYYY')) TABLESPACE users);

CREATE INDEX tab1_code_idx ON tab1(code) LOCAL;

INSERT INTO tab1
SELECT level,
       CASE
         WHEN MOD(level,2)=0 THEN 'CODE1'
         ELSE 'CODE2'
       END,
       CASE
         WHEN MOD(level,2)=0 THEN 'Description for CODE1'
         ELSE 'Description for CODE2'
       END,
       CASE
         WHEN MOD(level,2)=0 THEN SYSDATE
         ELSE ADD_MONTHS(SYSDATE, 12)
       END
FROM   dual
CONNECT BY level <= 100000;
COMMIT;

EXEC DBMS_STATS.gather_table_stats(USER, 'TAB1');

This is because of this:

         WHEN MOD(level,2)=0 THEN SYSDATE
         ELSE ADD_MONTHS(SYSDATE, 12)

SYSDATE will be inserted into tab1_part_2017 and ADD_MONTHS(SYSDATE, 12) will be inserted into tab1_part_2018 and you will get equal number of rows in both partitions (50000 in each to be exact).

Fix 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.

«inserted partition key does not map to any partition»

*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

Попробовать перевести

Поискать эту ошибку на форуме

Перечень ошибок взят из официального перечня ошибок Oracle версии 11R2.
Если не можете справиться — поищите эту ошибку на форуме по ссылке выше, многие ошибки мы уже разобрали.
Не получается — обязательно напишите, разберем, попробуем, пощупаем, понюхаем.
Главное — не паникуйте, но всегда, в первую очередь убедитесь, что бекап есть и сделайте бекап того, что сейчас имеете 🙂