Skip to Main Content
  • Questions
  • Script to compare data in all tables in two different databases, if the table is present in both the databases.

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Jince.

Asked: August 08, 2016 - 9:50 pm UTC

Last updated: August 09, 2016 - 3:51 pm UTC

Version: Oracle Database 11g

Viewed 50K+ times! This question is

You Asked

Hi,

I am looking for a stored procedure to compare the data in all the tables in two different databases.

I have 2 databases DB1 and DB2.

From DB1, a Dblink is created to access DB2.

First step is
- to find all the tables that exists in both the dbs, something like below.

Select OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE
From ALL_TAB_COLUMNS TABS
WHERE TABS.OWNER in ('SIEBEL')
INTERSECT
Select OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE
From ALL_TAB_COLUMNS@DB2 TABS
WHERE TABS.OWNER in ('SIEBEL')

Once we have the list of tables, we should get all the columns names and compare the data in all the tables.

It will be of great help if you could help with a stored procedure to do this, so that this can be scheduled to run once in a week.


and Connor said...

I assume you are wanting to be alerted if there is a difference. In which case, I would not bother with columns at this stage.

Each query can be:

select count(*)
from
(
  ( select * from t
    minus
    select * from t@db2 )
  union all
  ( select * from t@db2 
    minus
    select * from t)
) 
<code>

So thats very easy to turn into a PLSQL block

<code>
SQL> set serverout on
SQL> declare
  2    template varchar2(1000) :=
  3  'select count(*)
  4  from
  5  (
  6    ( select * from ##TABLE##
  7      minus
  8      select * from ##TABLE##@db2 )
  9    union all
 10    ( select * from ##TABLE##@db2
 11      minus
 12      select * from ##TABLE##)
 13  )';
 14
 15    l_sql varchar2(1000);
 16    l_result int;
 17
 18  begin
 19   for i in ( select table_name from user_tables )
 20   loop
 21     begin
 22       l_sql := replace(template,'##TABLE##',i.table_name);
 23       execute immediate l_sql into l_result;
 24
 25       if l_result != 0 then
 26         dbms_output.put_line('WARNING: '||i.table_name);
 27       end if;
 28     exception
 29       when others then
 30         dbms_output.put_line('PROBABLE MISMATCH ERROR: '||i.table_name);
 31     end;
 32   end loop;
 33  end;
 34  /

PL/SQL procedure successfully completed.



Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Compare using group by

George Joseph, August 09, 2016 - 4:48 am UTC

You could try the method of comparing by using UNION ALL and GROUP BY as mentioned in the below link.

http://www.oracle.com/technetwork/issue-archive/2010/10-sep/o15asktom-084959.html
Connor McDonald
August 09, 2016 - 3:51 pm UTC

Yes, that way can be much more efficient.

Chris

Generating SQL to compare tables

Stew Ashton, August 09, 2016 - 8:55 am UTC

The reviewer just above me recommended Tom Kyte's GROUP BY method for comparing tables. I wrote a package that generates the SQL for comparing two tables using this method. See

https://stewashton.wordpress.com/2015/01/21/stew_compare_sync-introducing-the-package/

Example using SCOTT.EMP:

select compare_sync.compare_sql('SCOTT', 'EMP', 'SCOTT.EMP') from dual;

select
  "EMPNO", "ENAME", "JOB", "MGR", "HIREDATE", "SAL", "COMM", "DEPTNO",
sum(OLD_CNT) OLD_CNT, sum(NEW_CNT) NEW_CNT
FROM (
  select
  "EMPNO", "ENAME", "JOB", "MGR", "HIREDATE", "SAL", "COMM", "DEPTNO",
  1 OLD_CNT, 0 NEW_CNT
  from SCOTT.EMP O
  union all
  select
  "EMPNO", "ENAME", "JOB", "MGR", "HIREDATE", "SAL", "COMM", "DEPTNO",
  0 OLD_CNT, 1 NEW_CNT
  from SCOTT.EMP N
)
group by
  "EMPNO", "ENAME", "JOB", "MGR", "HIREDATE", "SAL", "COMM", "DEPTNO"
having sum(OLD_CNT) != sum(NEW_CNT)
order by 1, NEW_CNT;
To generate the SQL queries for every table in a schema:
select compare_sync.compare_sql('SCOTT', table_name, 'SCOTT.'||table_name)
from all_tables where owner = 'SCOTT';
There is an option to limit the comparison to specific columns if you need it.
Connor McDonald
August 09, 2016 - 3:50 pm UTC

Thanks for sharing Stew

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library