set showmode off echo off
set heading off pagesize 0 timing off feedback off linesize 160 trimspool on
rem
rem Script: comptables.sql
rem
rem Purpose: Create SQL to compare tables in the source (current; such
rem    as test) and target (such as prod) databases and show the records
rem    in the selected database (such as target) that don't match their
rem    corresponding records in the other database (assuming the first
rem    column and all unique index columns of the tables match).  You
rem    must be in the source database and have a database link to the
rem    target database created as user system, as in:
rem      create database link prod connect to system identified by pswd
rem        using 'tnsnames instance name for prod';
rem    replacing pswd with your production database's system password, and
rem    using your instance name in $ORACLE_HOME/network/admin/tnsnames.ora.
rem    The SQL created has all fields like DATE and USER commented out.
rem    If you want to compare those fields, just remove '--' from the
rem    resulting SQL after running it and rerun the edited sql (or, you
rem    could edit the resulting comptables_do.sql and then run that).
rem    Only character, number, and date type fields are compared.  LONG's,
rem    BLOB's, and other similar fields are ignored.  Also, records in one
rem    database that aren't in the other database are ignored.
rem
rem Author: Stephen Rea
rem    Maristream, Inc.
rem Released: 10/17/00
rem
rem Upgrades:
rem    4/19/01 - Added code for null value comparisons (character types).
rem    4/24/01 - Get target database link name from dba_db_links, and allow
rem              the link name to be overridden.  Prompt for either the source
rem              database or target database mismatching records to be listed.
rem    7/1/03  - Prompt for table owner.
rem
accept tablename char prompt 'Enter table name to compare: '
set termout off verify off
column tablename new_value tablename
select upper('&tablename') tablename from dual;
define tableowner = 'DUMMY'
column tableowner new_value tableowner
select owner tableowner from dba_tables
     where table_name = '&tablename' and rownum = 1;
select username tableowner from user_users
     where '&tableowner' = 'DUMMY' and rownum = 1;
set termout on
accept tableowner2 char prompt 'Enter table owner, if not &tableowner: '
set termout off
select upper(decode('&tableowner2','','&tableowner','&tableowner2'))
     tableowner from dual;
define targetlink=UNDEFINED
column targetlink new_value targetlink
select substr(db_link,1,instr(db_link || '.','.')-1) targetlink from dba_db_links
where rownum < 2;
define thisdb=''
column thisdb new_value thisdb
select substr(substr(global_name,1,30),1,instr(substr(global_name,1,30) || '.',
'.')-1) thisdb from global_name;
set termout on
accept targetlink2 char prompt 'Enter target database link name, if not &targetlink: '
set termout off
select decode('&targetlink2','','&targetlink','&targetlink2') targetlink from dual;
set termout on
accept showthis char prompt 'Show unmatching records in &thisdb (Y or N; otherwise shows &targetlink)? '
set termout off
column showthis new_value showthis
select decode(substr('&showthis',1,1),'Y','Y','y','Y','N') showthis from dual;
set recsep off
col column_id noprint
!echo Generating comparison sql ...
spool comptables_do.sql
select -200 as column_id,
'select * from ' || owner || '.' || table_name ||
decode('&showthis','N','@&targetlink','') ||
' where ' || column_name
from dba_tab_columns where table_name = '&tablename'
and owner = '&tableowner'
and column_id = 1
union all
select -200 + column_id,
'|| ' || column_name
from dba_tab_columns where table_name = '&tablename'
and owner = '&tableowner'
and column_id > 1
and column_name in (select column_name from dba_ind_columns
where table_name = '&tablename' and table_owner = '&tableowner'
and index_name in (select index_name from dba_indexes
where table_name = '&tablename' and table_owner = '&tableowner'
and uniqueness = 'UNIQUE'))
union all
select -100,
'in (select sourcedb.' || column_name
from dba_tab_columns where table_name = '&tablename'
and owner = '&tableowner'
and column_id = 1
union all
select -100 + column_id,
'|| sourcedb.' || column_name
from dba_tab_columns where table_name = '&tablename'
and owner = '&tableowner'
and column_id > 1
and column_name in (select column_name from dba_ind_columns
where table_name = '&tablename' and table_owner = '&tableowner'
and index_name in (select index_name from dba_indexes
where table_name = '&tablename' and table_owner = '&tableowner'
and uniqueness = 'UNIQUE'))
union all
select column_id,
'from ' || owner || '.' || table_name || ' sourcedb,' ||
owner || '.' || table_name || '@&targetlink targetdb' || chr(10) ||
'where sourcedb.' || column_name || ' = targetdb.' || column_name
from dba_tab_columns where table_name = '&tablename'
and owner = '&tableowner'
and column_id = 1
union all
select column_id,
'and sourcedb.' || column_name || ' = targetdb.' || column_name
from dba_tab_columns where table_name = '&tablename'
and owner = '&tableowner'
and column_id > 1
and column_name in (select column_name from dba_ind_columns
where table_name = '&tablename' and table_owner = '&tableowner'
and index_name in (select index_name from dba_indexes
where table_name = '&tablename' and table_owner = '&tableowner'
and uniqueness = 'UNIQUE'))
union all
select 100,'and (1 <> 1' from dual
union all
select column_id + 100,
'  or ' || decode(instr(data_type,'CHAR'),0,'','nvl(') || 'sourcedb.' ||
column_name || decode(instr(data_type,'CHAR'),0,'',',''~'')') ||
' <> ' || decode(instr(data_type,'CHAR'),0,'','nvl(') || 'targetdb.' ||
column_name || decode(instr(data_type,'CHAR'),0,'',',''~'')')
from dba_tab_columns where table_name = '&tablename'
and owner = '&tableowner'
and column_id > 1
and column_name not in (select column_name from dba_ind_columns
where table_name = '&tablename' and table_owner = '&tableowner'
and index_name in (select index_name from dba_indexes
where table_name = '&tablename' and table_owner = '&tableowner'
and uniqueness = 'UNIQUE'))
and data_type in ('CHAR','NCHAR','NUMBER','NVARCHAR2','VARCHAR2')
and column_name not like '%USER%'
union all
select column_id + 100,
'  -- or sourcedb.' || column_name || ' <> targetdb.' || column_name
from dba_tab_columns where table_name = '&tablename'
and owner = '&tableowner'
and column_id > 1
and column_name not in (select column_name from dba_ind_columns
where table_name = '&tablename' and table_owner = '&tableowner'
and index_name in (select index_name from dba_indexes
where table_name = '&tablename' and table_owner = '&tableowner'
and uniqueness = 'UNIQUE'))
and not (data_type in ('CHAR','NCHAR','NUMBER','NVARCHAR2','VARCHAR2')
and column_name not like '%USER%')
union all
select 999,'));' from dual
order by 1;
spool off
!echo
!cat comptables_do.sql
!echo
set termout on verify on
accept run_it char prompt 'Run it (y or n)? '
set termout off verify off
!echo
spool comptables_do2.sql
select decode('&run_it','y','@comptables_do.sql','Y','@comptables_do.sql','')
from dual;
spool off
set recsep wrap
col column_id clear
set linesize 80 heading on pagesize 24 timing on feedback 6 trimspool off
set termout on verify on echo on showmode both
@comptables_do2.sql