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:

Shony said...

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

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

yanmaneee said...

nike vapormax
balenciaga
supreme clothing
westbrook shoes
coach handbags
yeezy shoes
jordan retro
nike vapormax
cheap jordans
nike lebron 16

Mohamed Abdellatif said...

حشرات الفراش حشرات الفراش والمسماة بالبق هي حشرات بيضويّة الشكل، وصغيرة الحجم، ولا تطير، وتتغذّى على دم الإنسان، وعلى الرغم من أنّها توجد في كافّة أنحاء المنزل كالشقوق الموجودة في الأثاث، وكافّة الأنسجة القماشيّة إلا أنّه يمكن إيجادها بشكل أكبر داخل غرف النوم، والأسرة، والمراتب، وإطارات الأسرّة

شركة مكافحة النمل الابيض بالاحساء
شركة مكافحة النمل الابيض بالقصيم
شركة مكافحة النمل الابيض بخميس مشيط
شركة مكافحة النمل الابيض بالخبر

Unknown said...

e3o93j6j51 y4i11b2p82 b3h15i6e08 d2l29d1p73 l0i08s0z37 y4x43b0z96