How to Compare Row Counts Between Two Databases

  • by
Several years ago I made a stored procedure for comparing table row counts between two databases after migration, which can output a very clear and formatted report to prove our hard work is done well.

In fact, I seldom use it. Because the comparing job becomes a part of my work, I need another shell script for faster comparing.

In this script, I just diff source and target result files for clarifying the difference. The output may not be reader-friendly like the output of the stored procedure that I made before, but it's enough for me to use it everyday.
[oracle@test ~]$ vi compare_data_imported.sh
#!/bin/bash
date > compare_data_imported_result_`date +'%Y%m%d'`.txt
sqlplus -s system/oracle << EOF
set echo off
set pagesize 0
set heading off;
set feedback off;
set linesize 200;
column stmts format a200;

spool erpapp_data.sql
select 'select ' || '''' || owner || ''' owner, ' || '''' || table_name || ''' table_name, count(*) cnt from "' || owner || '"."' || table_name || '";' stmts from dba_tables where owner in ('ERPAPP') order by owner, table_name;
spool off

spool erpapp_source_data.sql
select 'select ' || '''' || owner || ''' owner, ' || '''' || table_name || ''' table_name, count(*) cnt from "' || owner || '"."' || table_name || '"@source_db_link;' stmts from dba_tables where owner in ('ERPAPP') order by owner, table_name;
spool off

column owner format a15;
column table_name format a35;
column cnt format 99999999999;

spool erpapp_data.txt
@erpapp_data.sql
spool off

spool erpapp_source_data.txt
@erpapp_source_data.sql
spool off
exit
EOF
diff erpapp_source_data.txt erpapp_data.txt >> compare_data_imported_result_`date +'%Y%m%d'`.txt
date >> compare_data_imported_result_`date +'%Y%m%d'`.txt

In the above statement, ERPAPP is the schema that we want to compare.

After comparing, you should inspect the difference.
[oracle@test ~]$ ./compare_data_imported.sh
...
[oracle@test ~]$ less compare_data_imported_result_`date +'%Y%m%d'`.txt
...

Leave a Reply

Your email address will not be published. Required fields are marked *