OraFAQ Forum: Server Utilities » data pump import order

Home » RDBMS Server » Server Utilities » data pump import order - causing constraint violation (oracle 10.2.0.4.0 on Linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

data pump import order - causing constraint violation [message #482883]

Tue, 16 November 2010 10:58

kumarsz
Messages: 180
Registered: March 2005
Location: canada

Senior Member

Hello

We are trying to import data into existing tables in a schema using data pump

However the foreign key tables are being imported first and then the master table data thus violating the constraints

Apparently it seems larger tables are being imported first regardless of referential integrity constraints thus causing constraint violation (contrary to my understanding)

Is it a normal behaviour during data pump import?

Is there any workaround for this?

Is it possible that the keys being sequence generated are causing this?

As I understand import will commit after each table
In that case can we defer commit at all at the expense of large undo, set constraints to deferrable and try the import?

Please advice

Regards,
Ps

Report message to a moderator

Re: data pump import order - causing constraint violation [message #482884 is a reply to message #482883]

Tue, 16 November 2010 11:03

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Is there any workaround for this?
impdp the Parent tables first.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: data pump import order - causing constraint violation [message #482885 is a reply to message #482884]

Tue, 16 November 2010 11:11

OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom

Senior Member

sorry to interrupt

BlackSwan- do you mean data pump does not take care of the integrity implicitly and 'ps' will need import each table individually like following?

impdp parent1
impdp child1
impdp parent2
impdp child2
or
impdp parent1
impdp parent2
impdp child1
impdp child2

Does the sequence of tables in parfile will have any effect?

- Orakaran

Report message to a moderator

Re: data pump import order - causing constraint violation [message #482886 is a reply to message #482885]

Tue, 16 November 2010 11:19

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>BlackSwan- do you mean data pump does not take care of the integrity implicitly
It depends.
If doing a schema import into an EMPTY schema, then utility takes care of integrity implicitly.
It does so by enabling CONSTRAINTs after data is loaded.
When importing into exist schema, user must work with or around existing CONSTRAINTs.

Report message to a moderator

Previous Topic:	CHARACTER SET CONVERSION PROBLEM
Next Topic:	copy table with special char between oracle 8 and 10

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 11:02:12 CDT 2024