-
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.
-
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?
-
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
-
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.
-
Excellent ideas!
Just curious, how do Disconnected Snapshot Replication and MERGE handle key duplication?
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|