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

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.