5 Replies Latest reply: Mar 4, 2013 6:27 AM by avish16 RSS

    Merging Different databases

    770741
      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
          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
            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
              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
                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
                  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.