Compare content of databases in Oracle

oracle

We have a request to build an ASP.Net application that will be used as an interface to launch commands on Oracle databases.

We need to find a way to compare the content of 2 databases. We prefer not to code this process. Instead we would like to rely on Oracle to do it (we would like to call Oracle commands).

What is the best approach for this?

We are using Oracle 10.x

Any help will be appreciated

Best Answer

Here is a brute force example that doesn't require modifications to the tables. It merely gives you information about what is different, it does not do anything to synchronize it.

Sample data:

--DROP TABLE tableA;
--DROP TABLE tableB;

CREATE TABLE tableA AS (
   SELECT level-1 ID, chr(level-1+70) bb, chr(level-1+100) cc FROM dual CONNECT BY level<=4
);

CREATE TABLE tableB AS (
   SELECT level ID, chr(level+70) data1, chr(level+100) cc FROM dual
   UNION ALL
   SELECT level+2 ID, chr(level+70) data1, chr(level+100) cc FROM dual CONNECT BY level<=3
);

SELECT * FROM tableA;
SELECT * FROM tableB;

Compare:

WITH UnionedRows AS
(
   SELECT * FROM tableA
   UNION
   SELECT * FROM tableB
)
SELECT ID, sum(MyCount),
   case
      when sum(MyCount) = 111 then 'In Table A and Table B - Identical.'
      when sum(MyCount) =  11 then 'In Table A.'
      when sum(MyCount) = 112 then 'In Table A and Table B - Different.'
      when sum(MyCount) = 101 then 'In Table B.'
      else 'Error.'
   end Status
FROM
(
   SELECT ID, count(*) MyCount FROM UnionedRows GROUP BY ID
   UNION ALL
   SELECT ID, 10 FROM tableA
   UNION ALL
   SELECT ID, 100 FROM tableB
) GROUP BY ID ORDER BY ID;

You will need to modify this code to use your primary keys, table names and database links, but the nice thing is that you don't need to list all the column names for every table.

Related Question