OraFAQ Forum: Server Utilities » DB Import with same schema but duplicate keys

Home » RDBMS Server » Server Utilities » DB Import with same schema but duplicate keys

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

DB Import with same schema but duplicate keys [message #452039]

Mon, 19 April 2010 12:40

atheis
Messages: 7
Registered: April 2010

Junior Member

I have two oracle 9i databases A and B with complete equal schema.
Only the data is different. I want to import all table data from A to B. The problem is that there are duplicate primary keys. Therefore I want to insert data with new primary keys (all referencing tables are concerned too). Are there any existing tools/solutions?

[Updated on: Mon, 19 April 2010 12:41]

Report message to a moderator

Re: DB Import with same schema but duplicate keys [message #452041 is a reply to message #452039]

Mon, 19 April 2010 12:49

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Truncate the tables in target databaase before importing.

Regards
Michel

Report message to a moderator

Re: DB Import with same schema but duplicate keys [message #452042 is a reply to message #452041]

Mon, 19 April 2010 12:56

atheis
Messages: 7
Registered: April 2010

Junior Member

but I want to keep the data in database B. Truncate will delete the tables.

Report message to a moderator

Re: DB Import with same schema but duplicate keys [message #452043 is a reply to message #452042]

Mon, 19 April 2010 12:57

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Import in another schema then use SQL to import in the original one using the sequence of this latter.

Regards
Michel

Report message to a moderator

Re: DB Import with same schema but duplicate keys [message #452159 is a reply to message #452043]

Tue, 20 April 2010 07:20

atheis
Messages: 7
Registered: April 2010

Junior Member

ok, but is there a generic way to do this?

It is a lot of work to update all foreign keys. I am looking for a generic solution.

Report message to a moderator

Re: DB Import with same schema but duplicate keys [message #452160 is a reply to message #452039]

Tue, 20 April 2010 07:25

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Design your system so you don't have to re-assign keys on import?

I doubt you'll find any generic solution as most people will avoid ever getting in this mess in the first place.

Report message to a moderator

Re: DB Import with same schema but duplicate keys [message #452161 is a reply to message #452160]

Tue, 20 April 2010 07:26

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

How about inserting via a database link? Doing so, you'd have control over what to insert and how.

Report message to a moderator

Re: DB Import with same schema but duplicate keys [message #452162 is a reply to message #452160]

Tue, 20 April 2010 07:30

atheis
Messages: 7
Registered: April 2010

Junior Member

sure, but real life is different from theory Wink

Report message to a moderator

Re: DB Import with same schema but duplicate keys [message #452163 is a reply to message #452039]

Tue, 20 April 2010 07:34

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

And sometimes in real life you discover that there is a really good reason for the theory - this'd be one of them.

Bottom line, you're going to have to write a lot of SQL to sort the mess out, either after importing into another schema as Michel suggested, or by skipping the import and using DB links as Littlefoot suggested.

Report message to a moderator

Re: DB Import with same schema but duplicate keys [message #452164 is a reply to message #452161]

Tue, 20 April 2010 07:37

atheis
Messages: 7
Registered: April 2010

Junior Member

I think my problem cannot be solved by database links, because this mechanism doesn't solve the problem of duplicate PK's and corresponding FK's

Report message to a moderator

Re: DB Import with same schema but duplicate keys [message #452165 is a reply to message #452159]

Tue, 20 April 2010 07:48

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

atheis wrote on Tue, 20 April 2010 14:20

ok, but is there a generic way to do this?

It is a lot of work to update all foreign keys. I am looking for a generic solution.

It is a generic way to do it!
In a futur version you can use Data Masking option (which can be used for other things than masking data), it is a paying option.
So you can build a similar product and sell it but you will have nothing for free, as you said there is work to do it.

Regards
Michel

Report message to a moderator

Re: DB Import with same schema but duplicate keys [message #452183 is a reply to message #452163]

Tue, 20 April 2010 08:31

atheis
Messages: 7
Registered: April 2010

Junior Member

yes it's a mess and I am the lucky one who hast to fix everything Mad

ok, I didn't understand what you meant with database links Smile

Now I see that it can be a workaround.

I am just reading something about oracle (advanced) data replication (link see below)

Oracle Data Warehouse can maybe solve the problem too..
Furthermore there is a package called

# DBMS_REPCAT.ADD_DELETE_RESOLUTION - DUP_VAL_ON_INDEX

still investigating Smile

links:
-----------------------
http://www.orafaq.com/wiki/Advanced_Replication_FAQ#What_is_the_difference_between_BASIC_and_ADVANCED_replication.3F

Report message to a moderator

Re: DB Import with same schema but duplicate keys [message #452185 is a reply to message #452165]

Tue, 20 April 2010 08:40

atheis
Messages: 7
Registered: April 2010

Junior Member

What do you mean with future version concerning data masking option? When would it be available?

Report message to a moderator

Re: DB Import with same schema but duplicate keys [message #452200 is a reply to message #452185]

Tue, 20 April 2010 10:42

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Latest 10.2.0.4 with Grid Control and 11g with an API (iirc).
I meant future for you as you are in 9i.

Regards
Michel

[Updated on: Tue, 20 April 2010 10:43]

Report message to a moderator

Previous Topic:	Export Schema failed
Next Topic:	Re: Create a External Table in Oracle 10g:==> ERROR: KUP-01005 (split from hijacked thread by bb)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 20 01:25:28 CDT 2024