SQL Script

Compare Two Databases

Updated : 28-September-2001
Version : 1.3

Description

Ever done a full database import to make a copy of a database and found that there has been hundreds of errors. You then go through each error and fix the problem and complete the import, but your never quite sure if you may have missed an errpor. This script compares all object, columns, constraints of two databases and reports all differences to give you some level of confidence that all database objects are as they should be.

Parameters

&dblink - The SQL*Net v2 TNS Entry of the source database
& passw - The system password of the remote database
&own - The database user to compare

SQL Source

set echo off verify off

col owner format a10
col object_type format a20
col object_name format a30

set pages 100
set lines 120

accept dblink prompt 'Enter the name of the remote database : '
accept passw  prompt 'Enter system password : ' hide
accept own    prompt 'Enter the owner name you want to check objects of  for all: '

spool chk2dbs.lst

drop database link &dblink;

create database link &dblink 
connect to system identified by &passw using '&dblink';

set heading off

select 'Objects in '||name||' which are not in &dblink'   from v$database ;

set heading on

select a.owner, a.object_type, a.object_name   from dba_objects a 
 where a.owner not in ('SYS','SYSTEM') and a.owner like UPPER('&own%')
   and not exists (select b.object_name     from dba_objects@&dblink b
   where b.owner = a.owner     and b.object_type = a.object_type
     and b.object_name = a.object_name)
 order by a.owner, a.object_type, a.object_name
/

set heading off

select 'Objects in &dblink which are not in '||name  from v$database ;

set heading on

select a.owner, a.object_type, a.object_name 
  from dba_objects@&dblink a 
 where a.owner not in ('SYS','SYSTEM') and a.owner like UPPER('&own%')
   and not exists (select b.object_name     from dba_objects b
   where b.owner = a.owner     and b.object_type = a.object_type
     and b.object_name = a.object_name)
 order by a.owner, a.object_type, a.object_name
/

column owner format a8
column null_text format a8
column column_name format a30
column data_type form a10 heading "Datatype"
column data_length form 999 Heading "LEN"
column data_precision form 9999 Heading "PREC"
column data_scale form 99999 HEading "SCALE"
column column_id form 999 heading "ID"set heading off

select 'Differences in Columns in &dblink which are not in '||name
  from v$database ;

set heading on
select a.owner,a.column_name, a.data_type, a.data_length, a.data_precision,
          a.data_scale, decode(a.nullable,'N','NOT NULL',NULL) null_text
          ,a.column_id, a.default_length from dba_tab_columns@&dblink a
 where a.owner not in ('SYS','SYSTEM') and a.owner like UPPER('&own%')
minus
select b.owner,b.column_name, b.data_type, b.data_length, b.data_precision,
          b.data_scale, decode(b.nullable,'N','NOT NULL',NULL) null_text
          ,b.column_id, b.default_length from dba_tab_columns b
 where b.owner not in ('SYS','SYSTEM') and b.owner like UPPER('&own%')
/

set heading off
select 'Differences in Columns in '||name||' which are not in &dblink'
  from v$database ;

set heading on

select a.owner,a.column_name, a.data_type, a.data_length, a.data_precision,
          a.data_scale, decode(a.nullable,'N','NOT NULL',NULL) null_text
          ,a.column_id, a.default_length from dba_tab_columns a
 where a.owner not in ('SYS','SYSTEM') and a.owner like UPPER('&own%') minus
select b.owner, b.column_name, b.data_type, b.data_length, b.data_precision,
          b.data_scale, decode(b.nullable,'N','NOT NULL',NULL) null_text
          ,b.column_id, b.default_length from dba_tab_columns@&dblink b
 where b.owner not in ('SYS','SYSTEM') and b.owner like UPPER('&own%')
/

set heading off
select 'Differences in Constraints in &dblink which are not in '||name
  from v$database ;

set heading on

select a.owner,a.constraint_name,a.table_name
from  dba_constraints@&dblink a
 where a.owner not in ('SYS','SYSTEM') and a.owner like UPPER('&own%')
and   a.constraint_type in ('P','R') minus
select b.owner,b.constraint_name,b.table_name from  dba_constraints b
 where b.owner not in ('SYS','SYSTEM') and b.owner like UPPER('&own%')
and   b.constraint_type in ('P','R')
/

set heading off

select 'Differences in Constraints in '||name||' which are not in &dblink' 
from v$database ;

set heading on

select a.owner,a.constraint_name,a.table_name
from  dba_constraints a
where a.owner not in ('SYS','SYSTEM') and a.owner like UPPER('&own%')
and   a.constraint_type in ('P','R') minus
select b.owner,b.constraint_name,b.table_name from  dba_constraints@&dblink b
where b.owner not in ('SYS','SYSTEM') and b.owner like UPPER('&own%')
and   b.constraint_type in ('P','R')
/

set heading off

select 'Differences in CHECK Constraints in &dblink which are not in '||name
  from v$database ;

set heading on

select a.owner,a.constraint_name,a.table_name
from  dba_constraints@&dblink a
where a.owner not in ('SYS','SYSTEM') and a.owner like UPPER('&own%')
and   a.constraint_type in ('C') and constraint_name not like 'SYS%'minus
select b.owner,b.constraint_name,b.table_name from  dba_constraints b
where b.owner not in ('SYS','SYSTEM') and b.owner like UPPER('&own%')
and   b.constraint_type in ('C') and constraint_name not like 'SYS%'
/

set heading off

select 'Differences in CHECK Constraints in '||name||' which are not in &dblink'
from v$database ;

set heading on

select a.owner,a.constraint_name,a.table_name
from  dba_constraints a
where a.owner not in ('SYS','SYSTEM') and a.owner like UPPER('&own%')
and   a.constraint_type in ('C') and constraint_name not like 'SYS%'minus
select b.owner,b.constraint_name,b.table_namefrom  dba_constraints@&dblink b
where b.owner not in ('SYS','SYSTEM') and b.owner like UPPER('&own%')
and   b.constraint_type in ('C') and constraint_name not like 'SYS%'
/

set heading off

select 'Differences in PROCEDURES in &dblink which are not in '||name
  from v$database ;

set heading on

select a.owner,a.name,a.type
from  dba_source@&dblink a
where a.owner not in ('SYS','SYSTEM') and a.owner = UPPER('&own%') minus
select b.owner,b.name,b.type from  dba_source b
where b.owner not in ('SYS','SYSTEM') and b.owner = UPPER('&own%')
/
set heading off
select 'Differences in PROCEDURES in '||name||' which are not in &dblink'
from v$database ;

set heading on

select a.owner,a.name,a.type from  dba_source a
where a.owner not in ('SYS','SYSTEM') and a.owner = UPPER('&own%') minus
select b.owner,b.name,b.type from  dba_source@&dblink b
where b.owner not in ('SYS','SYSTEM') and b.owner = UPPER('&own%')
/
REM drop database link &dblink;

spool off