Check Difference Between Two Database Objects

Step-1. Create Two DBLINK to connect with Two Database then

Step-2.

define logindb=DBLINKONE

define remotedb=DBLINKTWO

define schema_name=SCHEMA_NAME

clear col breaks compute

break on OBJECT_TYPE skip 1 

col src_name format a40

col tgt_name format a40

col object_type format a20

col src_length format 999,999

col tgt_length format 999,999

col diff format 999,999

 

With source_tbl as

(SELECT /*+ MATERIALIZE */ type, name

, standard_hash ( LISTAGG(text, ' ' ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY line) , 'SHA1') hash_value

, sum(length(text)) text_length

, max(line) source_lines

FROM     dba_source@&&logindb

where 1 = 1

and owner = '&&schema_name'

and name not like 'BIN$%'

--and type = 'PROCEDURE'

group by type, name)

, target_tbl as

(SELECT /*+ MATERIALIZE */ type, name

, standard_hash ( LISTAGG(text, ' ' ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY line) , 'SHA1') hash_value

, sum(length(text)) text_length

, max(line) source_lines

FROM     dba_source@&&remotedb

where 1 = 1

and owner = '&&schema_name'

and name not like 'BIN$%'

--and type = 'PROCEDURE'

group by type, name)

select nvl(a.type, b.type) object_type, a.name src_name, b.name tgt_name

, nvl(a.text_length,0) src_length, nvl( b.text_length,0) tgt_length 

, abs ( nvl(a.text_length,0) - nvl(b.text_length,0) )  diff

, GREATEST ( a.source_lines, b.source_lines) max_source_lines

, abs ( a.source_lines -  b.source_lines) diff_source_lines

from source_tbl A full outer join target_tbl B

on (a.name = b.name

    and a.type = b.type)

where 1= 1 

--and (a.hash_value <> b.hash_value

--    or nvl(a.text_length,0) <> nvl(b.text_length,0) )

--objects that are available in both environment

and (a.name is not null and b.name is not null)

and abs ( nvl(a.text_length,0) - nvl(b.text_length,0) ) > 10

order by nvl(a.type, b.type), nvl(a.name, b.name)

/