set pages 50
set lines 100
column column_name format a30;
prompt Enter Database Link Name
accept remotedb
spool diff.lis
Prompt           == Tables here but not there ==
select a.table_name from user_tables a
   where not exists(select 1 from user_tables@&remotedb b
                       where a.table_name = b.table_name)
order by 1
/

Prompt           == Columns here but not there or whose data type do not match ==
select a.table_name,a.column_name from user_tab_columns a
   where not exists(select 1 from user_tab_columns@&remotedb b
                       where a.table_name  = b.table_name
                         and a.column_name = b.column_name
                         and a.data_type   = b.data_type)
     and exists(select 1 from user_tables@&remotedb b
                       where a.table_name = b.table_name)
order by 1,2
/

Prompt           == Constraints here but not there ==
select a.table_name,a.constraint_name from user_constraints a
    where not exists(select 1 from user_constraints@&remotedb b
                       where a.table_name = b.table_name
                         and a.constraint_name = b.constraint_name)
     and exists(select 1 from user_tables@&remotedb b
                       where a.table_name = b.table_name)
          and a.constraint_name not like 'SYS%'
order by 1,2
/

Prompt           == Constraints here but not there or whose columns do not match ==
select a.table_name,a.constraint_name,a.column_name from user_cons_columns a
    where not exists(select 1 from user_cons_columns@&remotedb b
                       where a.table_name = b.table_name
                         and a.constraint_name = b.constraint_name
                         and a.column_name     = b.column_name
                         and a.position        = b.position)
          and a.constraint_name not like 'SYS%'
     and exists(select 1 from user_tables@&remotedb b
                       where a.table_name = b.table_name)
order by 1,2,3
/

Prompt           == Indexes here but not there == 
select a.index_name from user_indexes a
   where not exists(select 1 from user_indexes@&remotedb b
                       where a.index_name = b.index_name)
     and exists(select 1 from user_tables@&remotedb b
                       where a.table_name = b.table_name)
order by 1
/

Prompt           == Indexes here but not there or whose data type do not match ==
select a.table_name,a.index_name,a.column_name from user_ind_columns a
   where not exists(select 1 from user_ind_columns@&remotedb b
                       where a.table_name  = b.table_name
                         and a.column_name = b.column_name
                         and a.index_name  = b.index_name
                         and a.column_position = b.column_position)
     and exists(select 1 from user_tables@&remotedb b
                       where a.table_name = b.table_name)
order by 1,2,3
/

Prompt           == Sequences here but not there == 
select a.sequence_name from user_sequences a
   where not exists(select 1 from user_sequences@&remotedb b
                       where a.sequence_name = b.sequence_name)
order by 1
/

Prompt           == Views here but not there == 
select distinct a.view_name from user_views a
   where not exists(select 1 from user_views@&remotedb b
                       where a.view_name = b.view_name)
order by 1
/
spool off
undefine remotedb
