Thanks for your answer. adding additional info.
R K, September 03, 2015 - 7:26 pm UTC
The tables are not referential on primary keys etc. The tables can be joined based on ID columns when querying data. Basically there are two main Tables forexample, Table 1 and Table 2. The Table 1 Primary Key = (surrogate_id,version_number). Table 2 primary key = surrogate_id,version_number). there is Table1_ID column in Table1. Table2_ID and Table2_ID column in Table2. Same columns are present in rest of the tables. So when quering data, we only select data based on joins Table1_ID and Table2_ID columns. Surrogate_id is not used for joins.
September 04, 2015 - 3:13 am UTC
Can you please elaborate further with some scripts to create the tables, populate them with data, and show us an example of the queries you want to run.
Thanks
Oracle has a solution for this in 12c
V Giridaran, September 04, 2015 - 8:01 pm UTC
The sys_guid function will ensure unique ID generation across environments but it requires RAW datatype.
September 05, 2015 - 12:19 am UTC
(I'm still not sure of the original poster's requirement) but if the need for a unique sequences across environments, then simply offsetting each environment by an appropriate range would be appropriate (and still allow integer data types).
Hope this helps.