Ora-14400 inserted partition key does not map to any partition

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

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;

Fix ORA-14400: inserted partition key does not map to any partition

_____________________________________________________________________________________________________________________

Ora-14400 inserted partition key does not map to any partition

Problem description:

Insert to a partitioned table from the application is getting failed with following error message.

java.sql.SQLException: ORA-14400: inserted partition key does not map to any partition

Solution Description


First of you have to check whether in your partitioned table has a default partition. Default partition means all the records which are not belongs to any of the other partition will get inserted into the default partition. The range will be specified with a limit of MAXVALUE.

If this partition did not belong to your table, you will have to add the partition using alter tablecommand.

ALTER TABLE “MED_AUD_DOC"

ADD PARTITION "P_DEFAULT" VALUES LESS THAN (MAXVALUE)  

STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "TAB_MED_2XL"

LOB ("DOCUMENT") STORE AS SECUREFILE ( TABLESPACE "LOB_MEDI_2XL" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NONE

CACHE NOCOMPRESS DEDUPLICATE LOB STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;

Try it

_____________________________________________________________________________________________________________________

Website Stats

How do I fix Ora 14400 inserted partition key does not map to any partition?

So to fix it , add another partition, with high_value greater than the value which we are trying to insert. SQL> alter table RANGE_TAB add partition p4 values less than (to_date('01042016′,'ddmmyyyy')); SQL> insert into RANGE_TAB values(to_date('24032016′,'ddmmyyyy'),100);

Can we add partition existing table in Oracle?

Use the ALTER TABLE ADD PARTITION statement to add a new partition to the "high" end (the point after the last existing partition). To add a partition at the beginning or in the middle of a table, use the SPLIT PARTITION clause.