Home » RDBMS Server » Server Administration » Table Change Detection - Databases (Oracle 11g, R2, RHEL 6)
Table Change Detection - Databases [message #601247] Mon, 18 November 2013 22:33 Go to next message
saleh_binmasood
Messages: 30
Registered: October 2009
Member
Good Day Guys,

We have a database A and B, we copied all the data of A to B few months ago. In other words A and B were similar few months ago. But, since then some tables in A has been updated by users where as B remained same with no changes.
Now my question is, i want to implement all changes made in some tables from A to B, what is the best option to find out the tables that has gone any sort of change(insert/update/delete) and apply that to database B ?

Thanks for you inputs
With Regards
-AS
Re: Table Change Detection - Databases [message #601250 is a reply to message #601247] Mon, 18 November 2013 22:43 Go to previous messageGo to next message
BlackSwan
Messages: 22690
Registered: January 2009
Senior Member
the options are slim or none.

Do you have full backup from when B was created and all REDO files archived since that time?
Re: Table Change Detection - Databases [message #601271 is a reply to message #601247] Tue, 19 November 2013 01:25 Go to previous messageGo to next message
John Watson
Messages: 4489
Registered: January 2010
Location: Global Village
Senior Member
How about using set operators? These queries will give you a start:

select * from A minus select * from B;
select * from B minus select * from A;

Re: Table Change Detection - Databases [message #601281 is a reply to message #601271] Tue, 19 November 2013 03:17 Go to previous messageGo to next message
gazzag
Messages: 298
Registered: November 2010
Location: Bristol, UK
Senior Member
There is a "Database Diff" tool within Oracle SQL Developer which may be worth a look at.

HTH
-g
Re: Table Change Detection - Databases [message #601288 is a reply to message #601281] Tue, 19 November 2013 04:09 Go to previous messageGo to next message
cookiemonster
Messages: 10908
Registered: September 2008
Location: Rainy Manchester
Senior Member
PL/SQL Developer also has such a tool and I imagine toad does to.
Re: Table Change Detection - Databases [message #601832 is a reply to message #601247] Tue, 26 November 2013 04:39 Go to previous messageGo to next message
saleh_binmasood
Messages: 30
Registered: October 2009
Member
I really appreciate all the replies.
I tried the option of 'Database Diff' under Oracle SQL Developer. It gives the difference between the table structure eg. the table in database A used to have Varchar, but in database B it is Nvarchar. But it does not give me the number of changes in rows/data. I tried multiple times. If any one tried it please let me know guys.

Thanks
-AS
Re: Table Change Detection - Databases [message #601870 is a reply to message #601832] Tue, 26 November 2013 09:52 Go to previous messageGo to next message
BlackSwan
Messages: 22690
Registered: January 2009
Senior Member
>But it does not give me the number of changes in rows/data.

see http://www.orafaq.com/forum/mv/msg/190377/601271/#msg_601271
Re: Table Change Detection - Databases [message #601902 is a reply to message #601870] Wed, 27 November 2013 00:44 Go to previous messageGo to next message
saleh_binmasood
Messages: 30
Registered: October 2009
Member
The given link doesnot works
Re: Table Change Detection - Databases [message #601906 is a reply to message #601902] Wed, 27 November 2013 01:30 Go to previous messageGo to next message
John Watson
Messages: 4489
Registered: January 2010
Location: Global Village
Senior Member
saleh_binmasood wrote on Wed, 27 November 2013 06:44
The given link doesnot works

Yes it does.
Re: Table Change Detection - Databases [message #601910 is a reply to message #601906] Wed, 27 November 2013 02:04 Go to previous messageGo to next message
saleh_binmasood
Messages: 30
Registered: October 2009
Member
Actually its opening the same page which we are writing on.
Re: Table Change Detection - Databases [message #601911 is a reply to message #601910] Wed, 27 November 2013 02:07 Go to previous messageGo to next message
Littlefoot
Messages: 19476
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Yes, as it points you to review a message #601271.
Re: Table Change Detection - Databases [message #601913 is a reply to message #601911] Wed, 27 November 2013 02:39 Go to previous message
cookiemonster
Messages: 10908
Registered: September 2008
Location: Rainy Manchester
Senior Member
John Watsons first post in other words. You'll need to set up a DB link between the two databases.
Previous Topic: Database not working when network is offline
Next Topic: Table compression
Goto Forum:
  


Current Time: Fri Aug 22 19:24:26 CDT 2014

Total time taken to generate the page: 0.06101 seconds