Oracle Partition - Error ORA14400 - inserted partition key does not map to any partition

Question

I'm trying to insert information in a partition table, but I don't know what I'm doing wrong! Show me this error: ORA-14400: inserted partition key does not map to any partition"

The table dba_tab_partitions shows this informations below:

1   PDIA_98_20091023    0
2   PDIA_98_20091022    0
3   PDIA_98_20091021    0
4   PDIA_98_20091020    0
5   PDIA_98_20091019    0

Please help me rs

Guevara

you are trying to insert rows in partition that doesn't exists. Do you have default partition defined? create one default partition and check rows in that partition after your insert statement to determine if you need any new partitions added – Decoupage

@user3249825 Can you refer my updated answer? – Floccule

Answer 1

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.

Floccule

Answer 2

For this issue need to add the partition for date column values, If last partition 20201231245959, then inserting the 20210110245959 values, this issue will occurs.

For that need to add the 2021 partition into that table

ALTER TABLE TABLE_NAME ADD PARTITION PARTITION_NAME VALUES LESS THAN (TO_DATE('2021-12-31 24:59:59', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) NOCOMPRESS

Ethnography