Ora 14400 как исправить

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.

I have the table with the following colums:

foo integer default 0 
'ID', 'INTEGER'
'date','DATE'
'F','VARCHAR2(20 CHAR)'

I try to insert the following values:

insert into table (foo,id,date,F) values('1','4','01.01.2016','aa');

and I get the following error:

SQL-error: ORA-14400 inserted partition key does not map to any
partition
14400. 00000 – “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

What did I do wrong?

UPDATE:

call meta_ddl.create_table  ('table1','create table table1 (foo integer default 0)  $#TABLEOPTIONS#$');
call meta_ddl.add_column    ('table1','ID', 'INTEGER');
call meta_ddl.add_column_with_default('table1','DATE','DATE', 'SYSDATE', 1);
call meta_ddl.add_column    ('table1','F','VARCHAR2(20 CHAR)');

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.

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

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;