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.

6 comments:

S. Thomas said...

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

oakleyses said...

oakley sunglasses, prada handbags, oakley sunglasses, longchamp handbags, longchamp handbags, louboutin shoes, louis vuitton handbags, coach factory outlet, tiffany and co, coach purses, louis vuitton outlet, polo ralph lauren outlet, air max, prada outlet, longchamp outlet, oakley sunglasses cheap, ray ban sunglasses, louboutin outlet, michael kors outlet, michael kors outlet, tiffany and co, burberry outlet, christian louboutin shoes, coach outlet store online, jordan shoes, polo ralph lauren outlet, louboutin, kate spade handbags, michael kors outlet, coach outlet, air max, gucci outlet, michael kors outlet, ray ban sunglasses, chanel handbags, michael kors outlet, tory burch outlet, nike free, kate spade outlet, louis vuitton outlet, burberry outlet, louis vuitton outlet stores, louis vuitton, nike shoes, michael kors outlet

oakleyses said...

air max, hollister, true religion outlet, nike blazer, louboutin, ray ban sunglasses, polo ralph lauren, michael kors, true religion jeans, sac guess, sac longchamp, hogan outlet, ralph lauren, vans pas cher, sac louis vuitton, air max pas cher, nike free pas cher, nike free, air max, mulberry, nike roshe run, sac burberry, hollister, vanessa bruno, louis vuitton, lululemon, michael kors pas cher, oakley pas cher, air jordan, ray ban pas cher, new balance pas cher, polo lacoste, converse pas cher, north face, sac louis vuitton, michael kors, sac hermes, nike tn, timberland, louis vuitton uk, longchamp, true religion jeans, nike air max, air force, north face

oakleyses said...

air max, hollister, true religion outlet, nike blazer, louboutin, ray ban sunglasses, polo ralph lauren, michael kors, true religion jeans, sac guess, sac longchamp, hogan outlet, ralph lauren, vans pas cher, sac louis vuitton, air max pas cher, nike free pas cher, nike free, air max, mulberry, nike roshe run, sac burberry, hollister, vanessa bruno, louis vuitton, lululemon, michael kors pas cher, oakley pas cher, air jordan, ray ban pas cher, new balance pas cher, polo lacoste, converse pas cher, north face, sac louis vuitton, michael kors, sac hermes, nike tn, timberland, louis vuitton uk, longchamp, true religion jeans, nike air max, air force, north face

oakleyses said...

abercrombie and fitch, instyler, ghd, bottega veneta, ugg boots, jimmy choo outlet, soccer shoes, ugg pas cher, herve leger, beats by dre, birkin bag, abercrombie and fitch, north face jackets, soccer jerseys, mont blanc, rolex watches, lululemon outlet, celine handbags, nike roshe run, nike trainers, giuseppe zanotti, hollister, wedding dresses, nike huarache, mcm handbags, vans shoes, chi flat iron, babyliss pro, north face outlet, nike roshe, ugg australia, ugg, marc jacobs, barbour, nfl jerseys, p90x, new balance shoes, asics running shoes, ferragamo shoes, mac cosmetics, insanity workout, uggs outlet, reebok outlet, longchamp, valentino shoes

oakleyses said...

converse, air max, gucci, canada goose, juicy couture outlet, canada goose, wedding dresses, moncler, ralph lauren, lancel, montre homme, moncler, louboutin, oakley, karen millen, vans, coach outlet store online, air max, canada goose jackets, ugg, hollister clothing store, louis vuitton, baseball bats, hollister, rolex watches, juicy couture outlet, iphone 6 cases, canada goose uk, canada goose outlet, ugg, moncler, moncler outlet, timberland boots, hollister, supra shoes, moncler, canada goose, converse shoes, toms shoes, moncler, moncler, canada goose, ugg boots, ray ban, parajumpers, canada goose