1748216 Members
3389 Online
108759 Solutions
New Discussion юеВ

ORACLE databases merging

 
Enrico Venturi
Super Advisor

ORACLE databases merging

Dear all,
I have a question not strictly related to HP, but I'd like to use this forum to try solve it:
I have two instances of ORACLE DB; the schema is exactly the same in the two instances; in the tables there are several cross references (foreign keys) so, for example, table A has AId as unique key, table B has BId as unique key and refers table A by means of an attribute whose name i BintoA.
I wanto to generate a new DB instance where the A tables have been merged: the AId of the 2nd DB are increased of an offset then the rows are inserted "after" those ones of the 1st instance, is there any mechanism to update automatically the BintoA attributes of 2nd instance when the B rows are added to the merged DB?
Sorry if my question isn't clear enough or if this isn't the rigth place for such a question.
Thank you in advance
2 REPLIES 2
Bill Thorsteinson
Honored Contributor

Re: ORACLE databases merging

I haven't seen such a utility.

You could try this:
Copy both sets of data into different schemas
on the new database;
Write a procedure to increment the B keys in batches
of 1000 to 10000 rows and then commit the change;
Then merge one schema into the other.
Enable the contraints.

R. Allan Hicks
Trusted Contributor

Re: ORACLE databases merging

I'm not sure if I follow exactly what you are saying, so my apologzies if I'm totally off the mark.

It seems that there are two issues. The first is merging the tables and the second is the satisfaction of the foreign key constraint.

On merging the tables, I take it that the A tables in the two schema are identical. You might consider:

create table c as select * from schema_a.table_a where 1=2;

Obviously, no rows will be inserted since 1 never equals 2. Table c can be an alias in yet another instance, database schema etc. See using synonyms and database links to accomplish that.

You may have to write a sql script to handle the copying. The script will have to have a cursor to walk the source table and insert the retrieved values into the merged table. In addition, include any other special logic, like adding an offset based on the source table. Say, 1000 from table A 10000 from table B or whatever. Finally, build and exception catcher (exception when no_data_found then) to catch the attempt to insert a row in table A that violates the foreign key constraint.

The exception catcher will have to have the code to resolve the missing parent issue and re-attempt to insert the child row. The begin end needs to wrapped around the original insert statement so as not to disrupt the cursor loop.

Another method that might work effectively, at least for relatively small tables. Is to create a view where tables A, B, .... are merged with

create view table_c as
select * from table_a
union
select * from table_b
.etc.

If you have a column called 'id', you can get more sophisticated by:

create view table_c as
select id <> from table_a
union
select id+1000 <> from table_b
.... an so on.

I'm not sure if this is what you were asking. Feel free to respond with more info.

-Good Luck





"Only he who attempts the absurd is capable of achieving the impossible