merging oracle databases

Printable View

09-16-2003, 10:41 AM
Nahed

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.
09-16-2003, 11:04 AM
KenEwald

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?
09-16-2003, 12:48 PM
stmontgo

in 9i there is the merge command that does an update if the row exists or an insert if the row does not

steve
09-16-2003, 02:30 PM
efrijters

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
09-16-2003, 04:41 PM
KenEwald

Excellent ideas!

Just curious, how do Disconnected Snapshot Replication and MERGE handle key duplication?
09-17-2003, 04:35 AM
Nahed

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.
09-23-2003, 06:29 AM
efrijters

Quote:

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