Re: Script to compare two schemas

From: Jared Still <jkstill_at_cybcon.com>
Date: Fri, 25 May 2001 12:57:38 -0700
Message-ID: <F001.0030FE9C.20010525115737@fatcity.com>

On Thursday 24 May 2001 17:20, Helmut Daiminger wrote: > Does anybody out there have a script to compare to database schemas and > list the differences?

I'm forwarding a post I made on this topic a few days ago.

Jared

• Forwarded Message ---------- Date: Sun, 13 May 2001 21:44:53 -0700 To: "Oracle-L List, ORACLE-L_at_fatcity.com" <ORACLE-L_at_fatcity.com> Cc: SRAJENDRAN_at_nlfs.com, "Mohan, Ross MohanR_at_STARS-SMI.com" <MohanR_at_STARS-SMI.com>

The database compare script didn't come out to well the first time, so I've just embedded them in this post.

The scripts are:

clears.sql
columns.sql
title.sql
schema_diff.sql

Jared

• title.sql - copied from title80.sql
  -- specify line width when calling
  
  • eg @title 'report heading' 90

rem TITLE.SQL   -     This SQL*Plus script builds a standard report
rem                   heading for database reports that are XX columns
rem
column  TODAY           NEW_VALUE       CURRENT_DATE            NOPRINT
column  TIME            NEW_VALUE       CURRENT_TIME            NOPRINT
column  DATABASE        NEW_VALUE       DATA_BASE               NOPRINT

set term off feed off
rem
define COMPANY = "BCBSO"
define HEADING = "&1"
col cPageNumLoc new_value PageNumLoc noprint select ('&&2' - 10 ) cPageNumLoc from dual; rem
TTITLE LEFT "Date: " current_date CENTER company col &&PageNumLoc "Page:" format 999 -

       SQL.PNO SKIP 1 LEFT "Time: " current_time CENTER heading RIGHT -
       format a15 SQL.USER SKIP 1 CENTER format a20 data_base SKIP 2

rem
rem
set heading off
set pagesize 0
rem
column passout new_value dbname noprint
SELECT TO_CHAR(SYSDATE,'MM/DD/YY') TODAY,

       TO_CHAR(SYSDATE,'HH:MI AM') TIME,
       --DATABASE||' Database' DATABASE,
       --rtrim(database) passout
       name||' Database' DATABASE,
       lower(rtrim(name)) passout

FROM v$database;
set term on feed on
rem
set heading on
set pagesize 58
set line &&2
set newpage 0
define db = '_&dbname'
undef 1 2

---

• columns.sql

col blocks format 99,999,999 head 'BLOCKS'

col db_link format a30 head 'DB LINK'
col db_link_instance format a30 head 'DB LINK|INSTANCE'
col db_link_username format a10 head 'DB LINK|USERNAME'

col empty_blocks format 99,999,999 head 'EMPTY BLOCKS' col host format a10 head 'HOST'
col index_name format a30 head 'INDEX NAME' col initial_extent format 9,999,999,999 head 'INITIAL|EXTENT' col instance format a4 head 'INST'

col max_blocks format 9,999,999 head 'MAX BLOCKS'
col max_bytes format 99,999,999,999 head 'MAX BYTES'
col max_extents format 999,999 head 'MAX|EXTENTS'
col min_extents format 999 head 'MIN|EXT'

col next_extent format 9,999,999,999 head 'NEXT|EXTENT' col object_name format a30 head 'OBJECT NAME' col owner format a10 head 'OWNER'

col pct_free format 999 head 'PCT|FREE'
col pct_increase format 999 head 'PCT|INC'
col pct_used format 999 head 'PCT|USED'

col synonym_name format a30 head 'SYNONYM NAME' col table_name format a30 head 'TABLE NAME' col table_owner format a10 head 'TABLE|OWNER' col tablespace_name format a15 head 'TABLESPACE|NAME' col timestamp format a19 head 'TIME STAMP' col username format a10 head 'USERNAME'
col last_ddl_time head 'LAST DDL|TIME'
col created head 'CREATED'
col procedure_name format a30 head 'PROCEDURE NAME' col function_name format a30 head 'FUNCTION NAME'

col package_name format a30 head 'PACKAGE NAME'
col package_body_name format a30 head 'PACKAGE BODY NAME'
col segment_name format a30 head 'SEGMENT NAME'

----------------------

• clears.sql set pause off set echo off set trimspool on set feed on term on echo off verify off set line 80 set pages 24 head on

clear col
clear break
clear computes

btitle ''
ttitle ''

btitle off
ttitle off

---

• schema_diff.sql
• Jared Still
• jkstill_at_bcbso.com
• jkstill_at_teleport.com -- --
• 07/14/1999 jkstill - removed table_owner from the index_diff
• section as this made all indexes appear
• in the difference report if the schemas
• had different names
• -
• this script will create reports of the
• differences between 2 schemas
• the reports created are:
• table_diff.txt
• index_diff.txt
• arg_diff.txt
• sequence_diff.txt
• all differences are checked both
• ways - schema1 is compared to schema2
• and then schema2 is compared to schema1
• it does not matter which database the
• reports are initiated from
• requirements: --
• either global links or database links to
• the schemas to be compared --
• dba privileges --
• this report creates temporary tables for it's reports
• they are left intact for your perusal
• tables:
• arg_diff
• index_diff
• table_diff --
• REPORT SUMMARY
• table_diff.txt
• this report compares tables from 2 schemas
• included are
• tables/columns that appear in a table in
• one schema, and not the other --
• any differences between columns; includes
• order of column, type and size.
• index_diff.txt
• this report compares indexes from 2 schemas
• included are
• indexes that appear in one schema
• and not the other --
• any differences between index structures
• does not check for storage parms
• arg_diff.txt
• this report compares arguments from 2 schemas
• included are
• all procedures/packages/functions and their
• arguments ( if any ) that appear in one schema
• and not the other --
• any differences in arguments --
• note: this currently will not find packages
• created for global variables
• i.e.: those packages created without
• a body and whose header contains
• only variables
• sequence_diff.txt
• this report compares sequences from 2 schemas --
• this report may include false positives since
• sequence information in the database is dynamic
• 06/15/1999 - added some more features

@clears
@columns

col cuser_1 noprint new_value uuser_1
col cuser_2 noprint new_value uuser_2
col cinstance_1 noprint new_value uinstance_1 col cinstance_2 noprint new_value uinstance_2

prompt
prompt
prompt schema_diff will report differences in tables prompt between any 2 users on any 2 databases prompt

set term on feed on
prompt User 1:
set term off feed off
select upper('&1') cuser_1 from dual;

set term on feed on
prompt instance 1:
set term off feed off
select upper('&2') cinstance_1 from dual;

set term on feed on
prompt User 2:
set term off feed off
select upper('&3') cuser_2 from dual;

set term on feed on
prompt instance 2:
set term off feed off
select upper('&4') cinstance_2 from dual;

set term on feed on

• do table diff prompt prompt Creating table difference data prompt

drop table table_diff;

create table table_diff as
select table_name, column_name, data_type, data_length, data_precision, data_scale
from all_tab_columns@&&uinstance_1
where 1=2
/

alter table table_diff add( instance varchar2(8) );

create index table_diff_idx on table_diff ( table_name, column_name, instance );

insert into table_diff ( table_name, column_name, data_type, data_length, data_precision, data_scale )

        select table_name, column_name, data_type, data_length, data_precision, data_scale

        from all_tab_columns@&&uinstance_1
        where owner = '&&uuser_1'
        minus
        select table_name, column_name, data_type, data_length, data_precision,
data_scale
        from all_tab_columns@&&uinstance_2
        where owner = '&&uuser_2'

/

update table_diff set instance = '&&uinstance_1';

insert into table_diff ( table_name, column_name, data_type, data_length, data_precision, data_scale )

        select table_name, column_name, data_type, data_length, data_precision, data_scale

        from all_tab_columns@&&uinstance_2
        where owner = '&&uuser_2'
        minus
        select table_name, column_name, data_type, data_length, data_precision,
data_scale
        from all_tab_columns@&&uinstance_1
        where owner = '&&uuser_1'

/

update table_diff set instance = '&&uinstance_2' where instance is null;

commit;

• do index diff prompt prompt Creating index difference data prompt

drop table index_diff;

create table index_diff as
select index_name, table_name, column_name, column_position, column_length from all_ind_columns@&&uinstance_1
where 1=2
/

alter table index_diff add( instance varchar2(8) );

create index index_diff_idx on index_diff (

        table_name,
        column_name, column_position, column_length

)
/

insert into index_diff ( index_name, table_name, column_name, column_position, column_length )

        select index_name,  table_name, column_name, column_position, column_length
        from all_ind_columns@&&uinstance_1
        where index_owner = '&&uuser_1'
        minus
        select index_name, table_name, column_name, column_position, column_length
        from all_ind_columns@&&uinstance_2
        where index_owner = '&&uuser_2'

/

update index_diff set instance = '&&uinstance_1';

insert into index_diff ( index_name, table_name, column_name, column_position, column_length )

        select index_name, table_name, column_name, column_position, column_length
        from all_ind_columns@&&uinstance_2
        where index_owner = '&&uuser_2'
        minus
        select index_name, table_name, column_name, column_position, column_length
        from all_ind_columns@&&uinstance_1
        where index_owner = '&&uuser_1'

/

update index_diff set instance = '&&uinstance_2' where instance is null;

commit;

• do argument diff prompt prompt Creating arguments difference data prompt

drop table arg_diff;

create table arg_diff as
SELECT

        obj# obj#
        ,procedure$ procedure
        ,argument argument
        ,type type

   ,overload# overload
   ,position position

        ,sequence# sequence

   ,default# "DEFAULT"
   ,in_out in_out
   ,level# "LEVEL"
        ,length length
   ,precision "PRECISION"

   ,scale scale
   ,radix radix
FROM sys.ARGUMENT$@&uinstance_1
where 1 = 2
/

alter table arg_diff add( instance varchar2(8), object_name varchar2(30), object_type varchar2(13) );
alter table arg_diff modify( obj# null );

create index arg_diff_idx on arg_diff ( obj#, argument, type );

insert into arg_diff

        (
                 object_name, object_type, procedure, argument, type, overload, 

position,
sequence,

                "DEFAULT", in_out, "LEVEL", length, "PRECISION", scale, radix
        )
        select
                obj.object_name ,obj.object_type ,arg.procedure$ ,arg.argument
                ,arg.type ,arg.overload# ,arg.position ,arg.sequence# ,arg.default#
,arg.in_out
        ,arg.level# ,arg.length ,arg.precision ,arg.scale ,arg.radix
        from sys.argument$@&&uinstance_1 arg, dba_objects@&&uinstance_1 obj
        where arg.obj# = obj.object_id
        and obj.owner = '&&uuser_1'
        minus
        select
                obj.object_name ,obj.object_type ,arg.procedure$ ,arg.argument
                ,arg.type ,arg.overload# ,arg.position ,arg.sequence# ,arg.default#
,arg.in_out
        ,arg.level# ,arg.length ,arg.precision ,arg.scale ,arg.radix
        from sys.argument$@&&uinstance_2 arg, dba_objects@&&uinstance_2 obj
        where arg.obj# = obj.object_id
        and obj.owner = '&&uuser_2'

/

update arg_diff
set instance = '&&uinstance_1'
/

• create tmp tables to do these updates
• it will be MUCH faster

drop table remote_dba_objects;
drop table remote_arguments;

create table remote_dba_objects
as
select *
from dba_objects@&&uinstance_1
/

create index remote_dba_object_idx1
on remote_dba_objects( object_type, object_id ) /

create index remote_dba_object_idx2
on remote_dba_objects( object_id )
/

create table remote_arguments
as
select

        obj#, procedure$,overload#,position,sequence#,level#,
        argument,type,default#,in_out,length,precision,scale,
        radix,deflength

from sys.argument$@&&uinstance_1
/

create index remote_arguments_idx1
on remote_arguments( argument, type )
/

update arg_diff diff
set obj# = (

        select obj#
        from remote_arguments arg, remote_dba_objects obj
        where arg.obj# = obj.object_id
        and obj.object_name = diff.object_name
        and obj.object_type = diff.object_type
        and obj.owner = '&&uuser_1'
        and nvl(arg.procedure$, 'NONE') = nvl(diff.procedure, 'NONE')
        and arg.argument = diff.argument
        and arg.type = diff.type
        and arg.overload# = diff.overload
        and diff.instance = '&&uinstance_1'

)
where instance = '&&uinstance_1'
/

insert into arg_diff

        (
                object_name, object_type, procedure, argument, type, overload, 

position,
sequence,

                "DEFAULT", in_out, "LEVEL", length, "PRECISION", scale, radix
        )
        select
                obj.object_name ,obj.object_type ,arg.procedure$ ,arg.argument
                ,arg.type ,arg.overload# ,arg.position ,arg.sequence# ,arg.default#
,arg.in_out
        ,arg.level# ,arg.length ,arg.precision ,arg.scale ,arg.radix
        from sys.argument$@&&uinstance_2 arg, dba_objects@&&uinstance_2 obj
        where arg.obj# = obj.object_id
        and obj.owner = '&&uuser_2'
        minus
        select
                obj.object_name ,obj.object_type ,arg.procedure$ ,arg.argument
                ,arg.type ,arg.overload# ,arg.position ,arg.sequence# ,arg.default#
,arg.in_out
        ,arg.level# ,arg.length ,arg.precision ,arg.scale ,arg.radix
        from sys.argument$@&&uinstance_1 arg, dba_objects@&&uinstance_1 obj
        where arg.obj# = obj.object_id
        and obj.owner = '&&uuser_1'

/

update arg_diff
set instance = '&&uinstance_2'
where instance is null
/

• create tmp tables to do these updates
• it will be MUCH faster

drop table remote_dba_objects;
drop table remote_arguments;

create table remote_dba_objects
as
select *
from dba_objects@&&uinstance_2
/

create index remote_dba_object_idx1
on remote_dba_objects( object_type, object_id ) /

create index remote_dba_object_idx2
on remote_dba_objects( object_id )
/

create table remote_arguments
as
select

        obj#, procedure$,overload#,position,sequence#,level#,
        argument,type,default#,in_out,length,precision,scale,
        radix,deflength

from sys.argument$@&&uinstance_2
/

create index remote_arguments_idx1
on remote_arguments( argument, type )
/

update arg_diff diff
set obj# = (

        select obj#
        from remote_arguments arg, remote_dba_objects obj
        where arg.obj# = obj.object_id
        and obj.object_name = diff.object_name
        and obj.object_type = diff.object_type
        and obj.owner = '&&uuser_2'
        and nvl(arg.procedure$, 'NONE') = nvl(diff.procedure, 'NONE')
        and arg.argument = diff.argument
        and arg.type = diff.type
        and arg.overload# = diff.overload
        and diff.instance = '&&uinstance_2'

)
where instance = '&&uinstance_2'
/

drop table remote_dba_objects;
drop table remote_arguments;

---

• reports

  ---

break on table_name on column_name on username

col data_precision format 999999 head 'PREC'
col data_scale format 999999 head 'SCALE'
col data_length format 999999 head 'LENGTH'

col column_name format a30 head 'COLUMN NAME'

set trimspool on

• table diff report

spool table_diff.txt

@title 'Table Diffs User1: &&uuser_1@&&uinstance_1 User2: &&uuser_2@&&uinstance_2' 120

select

        t1.table_name,
        t1.column_name,
        '&&uuser_1' username,
        instance,
        t1.data_type,
        t1.data_precision,
        t1.data_scale,
        t1.data_length

from all_tab_columns@&&uinstance_1 t1, table_diff d1 where owner = '&&uuser_1'

and t1.table_name  = d1.table_name
and t1.column_name = d1.column_name
and d1.instance = '&&uinstance_1'

union all
select

        t2.table_name,
        t2.column_name,
        '&&uuser_2' username,
        instance,
        t2.data_type,
        t2.data_length,
        t2.data_scale,
        t2.data_precision

from all_tab_columns@&&uinstance_2 t2, table_diff d2 where owner = '&&uuser_2'

and t2.table_name  = d2.table_name
and t2.column_name = d2.column_name
and d2.instance = '&&uinstance_2'

order by 1,2,3,4
/

• index diff report

break on index_name on table_name

col data_precision format 999999 head 'PREC'
col data_scale format 999999 head 'SCALE'
col data_length format 999999 head 'LENGTH'

col column_name format a30 head 'COLUMN NAME'

col column_position format 999 head 'COL|POS' col column_length format 99999 head 'COL|LEN'

@title 'Index Diffs User1: &&uuser_1@&&uinstance_1 User2: &&uuser_2@&&uinstance_2' 135

col username format a10 head 'INDEX|OWNER'

spool index_diff.txt

select

        t1.index_name,
        '&&uuser_1' username,
        instance,
        t1.table_name,
        t1.column_name,
        t1.column_position,
        t1.column_length

from all_ind_columns@&&uinstance_1 t1, index_diff d1 where index_owner = '&&uuser_1'

and t1.table_name  = d1.table_name
and t1.column_name = d1.column_name
and t1.column_position = d1.column_position
and t1.column_length = d1.column_length
and d1.instance = '&&uinstance_1'

union all
select

        t2.index_name,
        '&&uuser_2' username,
        instance,
        t2.table_name,
        t2.column_name,
        t2.column_position,
        t2.column_length

from all_ind_columns@&&uinstance_2 t2, index_diff d2 where index_owner = '&&uuser_2'

and t2.table_name  = d2.table_name
and t2.column_name = d2.column_name
and t2.column_position = d2.column_position
and t2.column_length = d2.column_length
and d2.instance = '&&uinstance_2'

order by 1,2,3,4,5,6
/

• argument diff report

col procedure format a40 head 'PROCEDURE'

break on procedure on overload

@title 'Argument Diffs User1: &&uuser_1@&&uinstance_1 User2: &&uuser_2@&&uinstance_2' 200

spool arg_diff.txt

SELECT

        substr(
                obj.OBJECT_NAME||
                decode( arg.procedure$,null,'','.') ||
                arg.procedure$,1,60
        ) procedure
   ,arg.OVERLOAD# overload
        ,arg.ARGUMENT
        ,diff.instance
        ,obj.object_type
        ,decode(arg.type,
                1, 'VARCHAR2',
                2, 'NUMBER',
                8, 'LONG',
                9, 'VARCHAR',
                12, 'DATE',
                23, 'RAW',
                24, 'LONG RAW',
                69, 'ROWID',
                96, 'CHAR',
                250, 'RECORD',
                252, 'BOOLEAN',
                -- just show type# as default
                substr(to_char(arg.type),1,10)
        ) type

   ,arg.POSITION
   ,NVL(arg.DEFAULT#,0) DEFAULT#

        ,decode(arg.IN_OUT,null,'IN', 2,'IN_OUT', 'UNKNOWN' ) IN_OUT    ,NVL(arg.LEVEL#,0) LEVEL#

        ,NVL(arg.LENGTH,0) LENGTH

   ,NVL(arg.PRECISION,0) PRECISION
   ,NVL(arg.SCALE,0) SCALE
   ,NVL(arg.RADIX,0) RADIX
from
        sys.argument$@&&uinstance_1 arg
        ,dba_objects@&&uinstance_1 obj
        ,arg_diff diff

where arg.obj# = obj.object_id
and obj.owner = '&&uuser_1'
and diff.obj# = arg.obj#
and nvl(diff.procedure,'NONE') = nvl(arg.procedure$,'NONE')

and diff.argument = arg.argument
and diff.type = arg.type
and diff.overload = arg.overload#
and diff.instance = '&&uinstance_1'

union all
SELECT

        substr(
                obj.OBJECT_NAME||
                decode( arg.procedure$,null,'','.') ||
                arg.procedure$,1,60
        ) procedure
   ,arg.OVERLOAD# overload
        ,arg.ARGUMENT
        ,diff.instance
        ,obj.object_type
        ,decode(arg.type,
                1, 'VARCHAR2',
                2, 'NUMBER',
                8, 'LONG',
                9, 'VARCHAR',
                12, 'DATE',
                23, 'RAW',
                24, 'LONG RAW',
                69, 'ROWID',
                96, 'CHAR',
                250, 'RECORD',
                252, 'BOOLEAN',
                -- just show type# as default
                substr(to_char(arg.type),1,10)
        ) type

   ,arg.POSITION
   ,NVL(arg.DEFAULT#,0) DEFAULT#

        ,decode(arg.IN_OUT,null,'IN', 2,'IN_OUT', 'UNKNOWN' ) IN_OUT    ,NVL(arg.LEVEL#,0) LEVEL#

        ,NVL(arg.LENGTH,0) LENGTH

   ,NVL(arg.PRECISION,0) PRECISION
   ,NVL(arg.SCALE,0) SCALE
   ,NVL(arg.RADIX,0) RADIX
from
        sys.argument$@&&uinstance_2 arg
        ,dba_objects@&&uinstance_2 obj
        ,arg_diff diff

where arg.obj# = obj.object_id
and obj.owner = '&&uuser_2'
and diff.obj# = arg.obj#
and nvl(diff.procedure,'NONE') = nvl(arg.procedure$,'NONE')

and diff.argument = arg.argument
and diff.type = arg.type
and diff.overload = arg.overload#
and diff.instance = '&&uinstance_2'

order by 1,2,3,4,5
/

• sequence diff report

@title 'Sequence Diffs User1: &&uuser_1@&&uinstance_1 User2: &&uuser_2@&&uinstance_2' 110

col sequence_name format a30 head 'SEQUENCE NAME' col last_number format 999999999999 head 'LAST|NUMBER' col min_value format 9999999999 head 'MIN|VALUE' col max_value format 999999999999 head 'MAX|VALUE' col increment_by format 999999 head 'INCREMENT'

col cache_size format 999999 head 'CACHE|SIZE'
col cycle_flag format a5 head 'CYCLE|FLAG'
col order_flag format a5 head 'ORDER|FLAG'

break on sequence_name skip 1

spool sequence_diff.txt

(

        (
                select
                        sequence_name, '&&uinstance_1' instance ,last_number, 
min_value,max_value,
                        increment_by,cycle_flag,order_flag,
                        cache_size
                from dba_sequences@&&uinstance_1
                where sequence_owner = '&&uuser_1'
        )
        minus
        (
                select
                        sequence_name, '&&uinstance_2' instance ,last_number, 
min_value,max_value,
                        increment_by,cycle_flag,order_flag,
                        cache_size
                from dba_sequences@&&uinstance_2
                where sequence_owner = '&&uuser_2'
        )

)
union
(

        (
                select
                        sequence_name, '&&uinstance_2' instance ,last_number, 
min_value,max_value,
                        increment_by,cycle_flag,order_flag,
                        cache_size
                from dba_sequences@&&uinstance_2
                where sequence_owner = '&&uuser_2'
        )
        minus
        (
                select
                        sequence_name, '&&uinstance_1' instance ,last_number, 
min_value,max_value,
                        increment_by,cycle_flag,order_flag,
                        cache_size
                from dba_sequences@&&uinstance_1
                where sequence_owner = '&&uuser_1'
        )

)
/

spool off

undef 1 2 3 4

prompt
prompt
prompt
prompt Your reports in the following files: prompt
prompt table_diff.txt
prompt index_diff.txt
prompt arg_diff.txt
prompt sequence_diff.txt
prompt

---

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Jared Still
  INET: jkstill_at_cybcon.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Fri May 25 2001 - 14:57:38 CDT