Replicating / Synchronising Auto ID fields to a CENTRAL Oracle Server

[transtec]

Well this is part of an assignment we hav to do so pls, any help wud be appreciated,

The architecture is such that there'll be a central Oracle databse running fulltime on the Main Site, whilst there'll be several Small Sites running a similar data model running Oracle allthruoghout. The catch here is that the Small Sites are supposed to run independently of the Main Site by running offline most of the time. That is they go online once a day to update the data to the Main Site.

This form of offline connectivity has led us to several problems. The major problem is that since we hope to generate and use Auto ID fields as keys for the table, this Auto ID fields could be duplicated across several sites ( meaning these keys for such tables aren't goin to be unique ). When we update the data from Small Sites to the Main Site there would surely be a conflict of Auto ID fields in the tables because each site would generate Auto IDs indepently of each other and thus the relationships between the data that was updated would be LOST !

So, we need a possible work around for this problem. That is a way in which we could update data from Small Sites to the Main Sites whilst maintaing the relationships across the data that was updated to the Main Site.

thanx in advance....

 

[SantaMufasa]

Trans,

Why not create the AutoID fields with a sequence number at each small site for rows added at that site, and combine the site sequence number with a siteID column, thus always ensuring uniqueness?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[sem]

Another idea is to use sequences with increment by some value starting with unique numbers less than that "step" value. In this case your systems needs about no redesign but the drawback is that it would be quite complex to separate data obtained from different instances (function-based indexes are required for productivity)

Regards, Dima

http://seminihin.narod.ru