Home » RDBMS Server » Server Utilities » data pump import order - causing constraint violation (oracle 10.2.0.4.0 on Linux)
data pump import order - causing constraint violation [message #482883] Tue, 16 November 2010 10:58 Go to next message
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
Re: data pump import order - causing constraint violation [message #482884 is a reply to message #482883] Tue, 16 November 2010 11:03 Go to previous messageGo to next message
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/
Re: data pump import order - causing constraint violation [message #482885 is a reply to message #482884] Tue, 16 November 2010 11:11 Go to previous messageGo to next message
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
Re: data pump import order - causing constraint violation [message #482886 is a reply to message #482885] Tue, 16 November 2010 11:19 Go to previous message
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.
Previous Topic: CHARACTER SET CONVERSION PROBLEM
Next Topic: copy table with special char between oracle 8 and 10
Goto Forum:
  


Current Time: Fri Sep 17 07:22:11 CDT 2021