Re: Data Migration options - oracle to oracle
From: Norman Dunbar <oracle_at_dunbar-it.co.uk>
Date: Fri, 06 Sep 2013 13:51:37 +0100
Message-ID: <5229CFD9.6070500_at_dunbar-it.co.uk>
On 06/09/13 13:30, rjamya wrote:
> Import using impdp with triggers and constraints in place is a disaster
> waiting to happen, especially since you CANNOT control the order in which
> impdp will import table data.
Date: Fri, 06 Sep 2013 13:51:37 +0100
Message-ID: <5229CFD9.6070500_at_dunbar-it.co.uk>
On 06/09/13 13:30, rjamya wrote:
> Import using impdp with triggers and constraints in place is a disaster
> waiting to happen, especially since you CANNOT control the order in which
> impdp will import table data.
Good point! Exp and expdp export tables in alphabetical order, so if there are ref int constraints in place when importing, and the child table is earlier in the sort order than the parent, and there are no parent rows for the child rows coming in, there will be a constraint violation.
Even if the constraints are DEFERRED, the checking takes place on the next commit, so if you have commit=y assuming imp, or where impdp commits at the end of each table, you will still get the constraint violation.
:-(
Cheers,
Norm.
-- Norman Dunbar Dunbar IT Consultants Ltd Registered address: 27a Lidget Hill Pudsey West Yorkshire United Kingdom LS28 7LG Company Number: 05132767 -- http://www.freelists.org/webpage/oracle-lReceived on Fri Sep 06 2013 - 14:51:37 CEST