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, [EMAIL PROTECTED]" <[EMAIL PROTECTED]>
Cc: [EMAIL PROTECTED], "Mohan, Ross [EMAIL PROTECTED]"
<[EMAIL PROTECTED]>
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
-- [EMAIL PROTECTED]
-- [EMAIL PROTECTED]
--
--
-- 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
