OraFAQ Forum: Server Administration » Table Change Detection

Home » RDBMS Server » Server Administration » Table Change Detection - Databases (Oracle 11g, R2, RHEL 6)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Table Change Detection - Databases [message #601247]

Mon, 18 November 2013 22:33

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

Report message to a moderator

Re: Table Change Detection - Databases [message #601250 is a reply to message #601247]

Mon, 18 November 2013 22:43

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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?

Report message to a moderator

Re: Table Change Detection - Databases [message #601271 is a reply to message #601247]

Tue, 19 November 2013 01:25

John Watson
Messages: 8960
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;

Report message to a moderator

Re: Table Change Detection - Databases [message #601281 is a reply to message #601271]

Tue, 19 November 2013 03:17

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

There is a "Database Diff" tool within Oracle SQL Developer which may be worth a look at.

HTH
-g

Report message to a moderator

Re: Table Change Detection - Databases [message #601288 is a reply to message #601281]

Tue, 19 November 2013 04:09

cookiemonster
Messages: 13959
Registered: September 2008
Location: Rainy Manchester

Senior Member

PL/SQL Developer also has such a tool and I imagine toad does to.

Report message to a moderator

Re: Table Change Detection - Databases [message #601832 is a reply to message #601247]

Tue, 26 November 2013 04:39

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

Report message to a moderator