- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- ORACLE databases merging
Operating System - HP-UX
1748216
Members
3389
Online
108759
Solutions
Forums
Categories
Company
Local Language
юдл
back
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
юдл
back
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Blogs
Information
Community
Resources
Community Language
Language
Forums
Blogs
Topic Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-20-2001 07:00 AM
тАО09-20-2001 07:00 AM
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
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-20-2001 05:08 PM
тАО09-20-2001 05:08 PM
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.
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-25-2001 10:24 AM
тАО09-25-2001 10:24 AM
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
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 <
union
select id+1000 <
.... 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
The opinions expressed above are the personal opinions of the authors, not of Hewlett Packard Enterprise. By using this site, you accept the Terms of Use and Rules of Participation.
News and Events
Support
© Copyright 2024 Hewlett Packard Enterprise Development LP