Tuesday, March 13, 2012

On ORA-14400: inserted partition key does not map to any partition using ImpDp (Data Pump)

After I have exported my partitioned tables I had to import into development database.
The command I used was (the dmp file was located on default DATA_PUMP_DIR directory):
impdp system@MY_DEV_INSTANCE dumpfile=20120312_SCHEMANAME_TABLENAME.dmp TABLE_EXISTS_ACTION=TRUNCATE

but the following error was found:
ORA-14400: inserted partition key does not map to any partition

This happens when some datas have to be inserted into a partition but that partition doesn't exist. My SCHEMANAME.TABLENAME table was already created last month but impdp was not able to create the missing partitions.
I solved simply dropping and creating again my table from the dump itself with the following command:
impdp system@MY_DEV_INSTANCE dumpfile=20120312_SCHEMANAME_TABLENAME.dmp sqlfile=SCHEMANAME_TABLENAME.ddl


Then I modified the SCHEMANAME_TABLENAME.ddl file adding a "future" partition:
CREATE TABLE "SCHEMANAME"."TABLENAME"
( ....
....
)
TABLESPACE "TSD_SCHEMANAME"
PARTITION BY RANGE ("CALLDATA")
( ....
....
PARTITION "P20120311" VALUES LESS THAN (TO_DATE(' 2012-03-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE "TSD_SCHEMANAME" COMPRESS ,
PARTITION "P20120312" VALUES LESS THAN (TO_DATE(' 2012-03-13 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE "TSD_SCHEMANAME" COMPRESS,
PARTITION P20150101 VALUES LESS THAN (MAXVALUE)
TABLESPACE "TSD_SCHEMANAME" COMPRESS);


In this way the second attempt to complete the import worked as expected.

1 comment:

S. Thomas said...

I found many useful datapump tips in this page.
http://www.acehints.com/p/site-map.html