Search code examples
oracledata-comparison

Compare two oracle database tables and it values in effective way

Two oracle database like DatabaseA and DatabaseB. Both are mirror database. Each database has 20 tables. DatabaseA is pointing to old approach Spring batch and It is same as production. Spring batch was modified for design problem. New Spring batch application is pointing to DatabaseB.

if I ran the same file both spring batch approaches, Both database should have same entries in table.

I need to compare all the table values are same in both approaches.

Is there any way to compare? Any tool to achieve this.?

Solution

  • 20 tables are not much, you can write a procedure for that case. If there are actually two databases, you must first set up a DB link between the databases.

    to compare you can use a minus operator

    select *
from(
      ( select * from tab1
         minus
        select * from tab2
      )
      union all
      ( select * from tab2
         minus
        select * from tab1
      )
)