DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: merging oracle databases

  1. #1
    Join Date
    Feb 2003
    Posts
    4

    Smile merging oracle databases

    Hello,
    Hope you can help me. I need to know how is it possible to merge 2 oracle databases together. Both databases have exactly the same tables and objects.
    Here is the scenario. One database is the production server database which is used in a multi-user environment. I have some users that need a copy of this production database on their laptops, to be updated every week so they can use it in the field. Is it possible to import a full dump of the production db to the local databases on their machines and merge the data with last weeks data..and so on?
    Or, do i have to always create a new blank database on the laptops and import the latest dump and dispose of the local db of last week?
    This would be too much work.
    I really am trying to find a way of synchronising the databases.

    Thanks for you help.

  2. #2
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    This is a classic problem and it's usually handled by importing to temporary tables, then merging with some smart code (eg. pl/sql package, sql script)

    Do you have primary keys on these tables?

    Consider how you would handle duplicates and how you would handle who wins when there is a duplicate key.

    Do you have timestamps on the table's rows?
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  3. #3
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    in 9i there is the merge command that does an update if the row exists or an insert if the row does not

    steve
    I'm stmontgo and I approve of this message

  4. #4
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    You need "Disconnected Snapshot Replication" for this problem

    Follow this link for the documentation: http://download-west.oracle.com/docs...ter_.htm#33063

    There you'll find (for instance):

    Support for Disconnected Snapshot Environments
    Snapshot replication allows users to remotely store all or a subset of replicated data from a master site in a disconnected environment. This scenario is typical of sales force automation systems where an individual's laptop (a disconnected device) stores a subset of data related to the individual salesperson.

    Happy reading!

    HTH,
    Erik
    Last edited by efrijters; 09-16-2003 at 03:02 PM.
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  5. #5
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Excellent ideas!

    Just curious, how do Disconnected Snapshot Replication and MERGE handle key duplication?
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  6. #6
    Join Date
    Feb 2003
    Posts
    4
    Thanks for the replies guys. All tables have referential constraints and most have timestamps. However, we are talking about over a hundred tables here. Thanks. I have Oracle 8i by the way.

  7. #7
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    Originally posted by KenEwald
    Excellent ideas!

    Just curious, how do Disconnected Snapshot Replication and MERGE handle key duplication?
    Maybe you can avoid a conflict by giving each representative a different start value in their sequences?

    Code:
           SEQUENCE 
    REP.   STARTS AT
    John     1000
    Mary     2000
    Dick     3000
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width