Home » SQL & PL/SQL » SQL & PL/SQL » updating table data between databases (2 threads merged by bb)
updating table data between databases (2 threads merged by bb) [message #357021] Mon, 03 November 2008 10:07 Go to next message
davidwoodbourne
Messages: 2
Registered: November 2008
Junior Member
Hi, We have a live database and a standby database. The standby database is replicated each night from live.
We want to switch the databases so that the standby database becomes live and vise-versa. The problem
we have is that new data will have been added to the live database which is not present in the standby
database when we switch them over.

I want to identify which records are missing from an individual table on standby and insert them from live
before the switch over. So far i have this:-

insert into table_A@remote_db(
select * from table_A
where id NOT IN (select id from table A
minus
select id from table_A@remote_db))


Is there a better way of writing this? Either via pl/sql (perhaps a cursor, or an array?) or SQL??
Re: updating table data between databases [message #357026 is a reply to message #357021] Mon, 03 November 2008 10:41 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this link and search for the keyword "Comparing the contents of two tables"

http://www.oracle.com/technology/oramag/oracle/05-jan/o15asktom.html

Regards

Raj
Re: updating table data between databases [message #357161 is a reply to message #357026] Tue, 04 November 2008 03:12 Go to previous messageGo to next message
davidwoodbourne
Messages: 2
Registered: November 2008
Junior Member
Hi Raj

thanks for the reply, i've had a look at the link, unfortunately it relates to the contents of rows, rather than the contents of the tables.

I need to be able to identify rows that are existing in one table but not in another and then insert the missing rows so both tables have the same records.

i.e. insert into tableB results of tableA-tableB

Thanks
Re: updating table data between databases (2 threads merged by bb) [message #357185 is a reply to message #357021] Tue, 04 November 2008 04:28 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why not start applying the redo logs to the standby database in real time, so that when you come to do the switchover the two databases are the sameOr
Previous Topic: write the query result in file and send the file in email attachement.
Next Topic: Need to determine interrelated rows in a table. Rank? PL/SQL? Not sure how to accomplish this
Goto Forum:
  


Current Time: Sat Dec 03 14:15:26 CST 2016

Total time taken to generate the page: 0.04146 seconds