This discussion is archived
5 Replies Latest reply: Mar 4, 2013 4:27 AM by avish16 RSS

Merging Different databases

770741 Newbie
Currently Being Moderated
Database Version: 11g

Hi Guys,

I have got a situation where I need to merge different databases (Oracle) into one.

Every database has same table structure.

I decided to take the dump from each database and create anew schema, create tables and import the

Data using logical backup that is .dmp

The situations i have figure out are as follows

1. Primary Key issue:

Let’s take a table 'A' in one database in another database also i have table 'A' but the sequences

Will be out of sync for that i have decided to use REMAP_DATA for generating different sequence numbers but need

Take care of child tables as well not sure how i can do that

1 abc

1 dfg

Merged data

1 abc

2 dfg

2. Duplicates:

Duplicates will not be inserted from .dmp files which is ok for me as i do not want duplicate data.

1 abc

1 abc

Merged data

1 abc


3. Sequence Difference:

1 abc

2 abc

Merged data:

1 abc

Any ideas for merging these cases will be appreciated

Thanks in advance
  • 1. Re: Merging Different databases
    avish16 Explorer
    Currently Being Moderated
    Are you trying to put all such tables in one db in one schema or one db and different schemas?

    If you are puting same name tables in different schemas in same db I dont think this will create any problem as it is just importing your schema dump in a fresh schema.

    If your are trying to put same name tables from different schemas in one single schema in a db then you need to check many a things and your dump import should fail for second import as the first dump will create the table.

    Can you please elaborate on the same.
  • 2. Re: Merging Different databases
    770741 Newbie
    Currently Being Moderated
    avish16 wrote:
    Are you trying to put all such tables in one db in one schema or one db and different schemas?

    If you are puting same name tables in different schemas in same db I dont think this will create any problem as it is just importing your schema dump in a fresh schema.

    If your are trying to put same name tables from different schemas in one single schema in a db then you need to check many a things and your dump import should fail for second import as the first dump will create the table.

    Can you please elaborate on the same.
    @avish16 : Thanks for the reply.

    My situation is i need to merge multiple databses into one different schema.

    so the first export and import of one database will go as needed as there will be no duplicates or no unique

    constraint violations.


    *"If your are trying to put same name tables from different schemas in one single schema in a db then you need to check many a things and your dump import should fail for second import as the first dump will create the table."*

    Note : i think we have an option of skipping the table creation and guide to import only data.
    The problem only occurs when i try to merge the data from second database into new merged schema.

    The situations will be the ones i mentioned in the last mail.

    1 abc

    1 dfg

    Merged data

    1 abc

    2 dfg

    ---------------------------
    1 abc

    1 abc

    Merged data

    1 abc

    ---------------------
    1 abc

    2 abc

    merged data

    1 abc
  • 3. Re: Merging Different databases
    avish16 Explorer
    Currently Being Moderated
    Ya thanks for pointing out correct, I missed the skip table create option, is there any possibility of you having a dblink and do select - insert into this schema while puting condn as desired, rather than taking these dumps for importing, that will reduce all such problems to minimal extent. Have you tried the same and faced any possible problems with it, as I dont think you could modify data while importing from dumps.
  • 4. Re: Merging Different databases
    Lakshmipathi Journeyer
    Currently Being Moderated
    Hi,

    You need to change the data in one database before taking the dump (export).
    or
    Need to disable the constaraints and load the data from the dump (import) and then by using SQL you can cahnge the data as you need.

    Regards,
    Lakshmipathi.
  • 5. Re: Merging Different databases
    avish16 Explorer
    Currently Being Moderated
    In any case you have to disable the constraints on the target table first and then insert the data and modify accordingly and then enable constraints. If you do export- import then too or else you use dblink and insert modified data and thereafter enable constraints.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points