compare_schemas.sql

oracle dba 常用的管理脚本, 覆盖日常的系统管理.

--* File Name    : Compare_Schemas.sql
--* Author       : Ottar Sorland (Oracle UK)
--* Description  : See file.
--* Last Modified: 15/07/2000
/* 
This Code entry comes from Ottar Sorland, a consultant for Oracle UK in Bracknell, United Kingdom. 
 
This script compares the object definitions in the current schema 
to that of a remote schema.
The remote schema is defined using a database link.
THE SCRIPT COMPARES THE FOLLOWING:
- Existence of tables
- Existence of columns
- Column definitions
- Existence of indexes
- Index definitions (column usage)
- Existence of constraints
- Constraint definitions (table, type and reference)
- Constraint column usage (for unique, primary key and foreign keys)
- Check constraint definitions
- Existence of triggers
- Definition of triggers
- Existence of procedure/packages/functions
- Definition of procedures/packages/functions
(Ie. the script does not do a complete check, it does not for example
check any grants, synonyms, clusters or storage definitions).
The script drops and creates a few temporary objects prefixed with
the first 3 letter combination (AAA - ZZZ) that does not conflict with any 
existing objects.
If you find ways of improving this script or have any comments and/or
problems, please send a mail to the author.
This script has been tested on Oracle 7.3.
*/
undef prex
undef prefx
undef a
undef thisuser
undef b
undef REMOTESCHEMA
undef REMOTEPASSW 
undef connstring 
undef c
undef todaysdate
variable prefx varchar2(3)
declare
i number ;
j number ;
k number ;
cnt number;
begin
 for i in ascii('A') .. ascii('Z') loop
  for j in ascii('A') .. ascii('Z') loop
   for k in ascii('A') .. ascii('Z') loop
     select count(*) into cnt from user_objects where object_name like
     chr(i)||chr(j)||chr(k)||'%';
     if cnt = 0 then
       :prefx := chr(i)||chr(j)||chr(k);  
       return;
     end if;
    end loop;
   end loop;
  end loop;
end;
/
column a new_val prex
set verify off
set linesize 132
set feedback off
select :prefx a from dual;
column b new_val thisuser
select user b from dual;
column c new_val todaysdate
select to_char(sysdate,'DD-MON-YYYY HH24:MI') c from dual;
accept REMOTESCHEMA char prompt 'Enter remote username:'
accept REMOTEPASSW char prompt 'Enter remote password:' hide
accept connstring char prompt 'Enter remote connectstring:'
spool dbdiff.txt
PROMPT
PROMPT
PROMPT  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT     SCHEMA DEFINITION DIFFERENCES     &todaysdate        
PROMPT          
PROMPT           this schema: &thisuser  
PROMPT         remote schema: &remoteschema.@&connstring
PROMPT  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT
PROMPT
create database link &prex.lnk connect to &REMOTESCHEMA identified 
by &REMOTEPASSW using '&CONNSTRING';
PROMPT
PROMPT
PROMPT  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT  TABLES MISSING IN THIS SCHEMA:
PROMPT  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
create table &prex.common_tables as
select table_name from user_TAbles@&prex.lnk
intersect
select table_name from user_tables;
select table_name from user_TAbles@&prex.lnk
minus
select table_name from &prex.common_tables;
PROMPT
PROMPT
PROMPT  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT TABLES MISSING IN REMOTE SCHEMA:
PROMPT  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select table_name from user_TAbles where table_name not like '&prex.%'
minus
select table_name from user_tables@&prex.lnk;
PROMPT
PROMPT
PROMPT  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT COLUMNS MISSING IN THIS SCHEMA FOR COMMON TABLES
PROMPT  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select table_name,column_name from user_tab_columns@&prex.lnk
where table_name in
(select table_name from &prex.common_tables)
minus
select table_name,column_name from user_tab_columns 
where table_name in
(select table_name from &prex.common_tables);
PROMPT
PROMPT
PROMPT  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT COLUMNS MISSING IN REMOTE SCHEMA FOR COMMON TABLES
PROMPT  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select table_name,column_name from user_tab_columns
where table_name in
(select table_name from &prex.common_tables)
minus
select table_name,column_name from user_tab_columns@&prex.lnk 
where table_name in
(select table_name from &prex.common_tables);

create table &prex.diff_cols1
( TABLE_NAME                   VARCHAR2(30),
 COLUMN_NAME                VARCHAR2(30),
 DATA_TYPE                      VARCHAR2(9),
 DATA_LENGTH                  NUMBER,
 DATA_PRECISION             NUMBER,
 DATA_SCALE                    NUMBER,
 NULLABLE                        VARCHAR2(1),
 COLUMN_ID                      NUMBER,
 DEFAULT_LENGTH            NUMBER,
 DATA_DEFAULT                 varchar2(2000));
create table &prex.diff_cols2
( TABLE_NAME                   VARCHAR2(30),
 COLUMN_NAME                VARCHAR2(30),
 DATA_TYPE                      VARCHAR2(9),
 DATA_LENGTH                  NUMBER,
 DATA_PRECISION             NUMBER,
 DATA_SCALE                    NUMBER,
 NULLABLE                        VARCHAR2(1),
 COLUMN_ID                      NUMBER,
 DEFAULT_LENGTH            NUMBER,
 DATA_DEFAULT                 varchar2(2000));
declare
cursor c1 is
select
 l.TABLE_NAME ,                    
 l.COLUMN_NAME,                    
 l.DATA_TYPE ,                    
 l.DATA_LENGTH,                    
 l.DATA_PRECISION ,                    
 l.DATA_SCALE ,                    
 l.NULLABLE,                    
 l.COLUMN_ID ,                    
 l.DEFAULT_LENGTH ,                    
 l.DATA_DEFAULT  
from user_tab_columns l,&prex.common_tables c
where c.table_name=l.table_name ;
TYPE rec is record (
 TABLE_NAME                   VARCHAR2(30),
 COLUMN_NAME                VARCHAR2(30),
 DATA_TYPE                      VARCHAR2(9),
 DATA_LENGTH                  NUMBER,
 DATA_PRECISION             NUMBER,
 DATA_SCALE                    NUMBER,
 NULLABLE                        VARCHAR2(1),
 COLUMN_ID                      NUMBER,
 DEFAULT_LENGTH            NUMBER,
 DATA_DEFAULT                 varchar2(2000)
);
c rec;
begin
 open c1;
 loop
   fetch c1 into c;
    exit when c1%NOTFOUND;
    insert into &prex.diff_cols1 values 
    (c.table_name,c.column_name,c.data_type,c.data_length,
     c.DATA_PRECISION, c.DATA_SCALE, c.NULLABLE, c.COLUMN_ID, 
     c.DEFAULT_LENGTH, c.DATA_DEFAULT);
end loop;
end;
/
declare
cursor c1 is
select
 l.TABLE_NAME ,                    
 l.COLUMN_NAME,                    
 l.DATA_TYPE ,                    
 l.DATA_LENGTH,                    
 l.DATA_PRECISION ,                    
 l.DATA_SCALE ,                    
 l.NULLABLE,                    
 l.COLUMN_ID ,                    
 l.DEFAULT_LENGTH ,                    
 l.DATA_DEFAULT  
from user_tab_columns@&prex.lnk l,&prex.common_tables c
where c.table_name=l.table_name ;
TYPE rec is record (
 TABLE_NAME                   VARCHAR2(30),
 COLUMN_NAME                VARCHAR2(30),
 DATA_TYPE                      VARCHAR2(9),
 DATA_LENGTH                  NUMBER,
 DATA_PRECISION             NUMBER,
 DATA_SCALE                    NUMBER,
 NULLABLE                        VARCHAR2(1),
 COLUMN_ID                      NUMBER,
 DEFAULT_LENGTH            NUMBER,
 DATA_DEFAULT                 varchar2(2000)
);
c rec;
begin
 open c1;
 loop
   fetch c1 into c;
    exit when c1%NOTFOUND;
    insert into &prex.diff_cols2 values 
    (c.table_name,c.column_name,c.data_type,c.data_length,
     c.DATA_PRECISION, c.DATA_SCALE, c.NULLABLE, c.COLUMN_ID, 
     c.DEFAULT_LENGTH, c.DATA_DEFAULT);
end loop;
end;
/
column table_name format a20
column column_name format a20
column param format a15
column local_value format a20
column remote_value format a20
set arraysize 1
set maxdata 32000
PROMPT
PROMPT
PROMPT  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT DIFFERENCE IN COLUMN-DEFS
PROMPT  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select l.table_name,l.column_name,'DATA_DEFAULT' param ,
l.DATA_DEFAULT local_value, r.DATA_DEFAULT remote_value
from &prex.diff_cols1 l, &prex.diff_cols2 r 
where l.table_name=r.table_name and 
      l.column_name=r.column_name and l.DATA_DEFAULT != r.DATA_DEFAULT
union
select l.table_name,l.column_name,'DATA_TYPE',l.data_type,r.data_type 
from &prex.diff_cols1 l, &prex.diff_cols2 r 
where l.table_name=r.table_name and 
      l.column_name=r.column_name and l.data_type != r.data_type
union
select l.table_name,l.column_name,'DATA_LENGTH',to_char(l.data_length),
to_char(r.data_length) 
from &prex.diff_cols1 l, &prex.diff_cols2 r 
where l.table_name=r.table_name and 
      l.column_name=r.column_name and l.data_length != r.data_length
union
select l.table_name,l.column_name,'DATA_PRECISION',
to_char(l.DATA_PRECISION),to_char(r.DATA_PRECISION) 
from &prex.diff_cols1 l, &prex.diff_cols2 r 
where l.table_name=r.table_name and 
      l.column_name=r.column_name and l.DATA_PRECISION != r.DATA_PRECISION
union
select l.table_name,l.column_name,'DATA_SCALE',to_char(l.DATA_SCALE),
to_char(r.DATA_SCALE) 
from &prex.diff_cols1 l, &prex.diff_cols2 r 
where l.table_name=r.table_name and 
      l.column_name=r.column_name and l.DATA_SCALE != r.DATA_SCALE
union
select l.table_name,l.column_name,'NULLABLE',l.NULLABLE,r.NULLABLE 
from &prex.diff_cols1 l, &prex.diff_cols2 r 
where l.table_name=r.table_name and 
      l.column_name=r.column_name and l.NULLABLE != r.NULLABLE
union
select l.table_name,l.column_name,'COLUMN_ID',to_char(l.COLUMN_ID),
to_char(r.COLUMN_ID) 
from &prex.diff_cols1 l, &prex.diff_cols2 r 
where l.table_name=r.table_name and 
      l.column_name=r.column_name and l.COLUMN_ID != r.COLUMN_ID
union
select l.table_name,l.column_name,'DEFAULT_LENGTH',to_char(l.DEFAULT_LENGTH),
to_char(r.DEFAULT_LENGTH) 
from &prex.diff_cols1 l, &prex.diff_cols2 r 
where l.table_name=r.table_name and 
      l.column_name=r.column_name and l.DEFAULT_LENGTH != r.DEFAULT_LENGTH
order by 1,2
/                
         
create table &prex.common_indexes as
select table_name, index_name from user_indexes@&prex.lnk
where table_name in (select table_name from &prex.common_tables)
intersect
select table_name, INdex_name from user_indexes
where table_name in (select table_name from &prex.common_tables);
PROMPT
PROMPT
PROMPT  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT INDEXES MISSING IN THIS SCHEMA FOR COMMON TABLES
PROMPT  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select table_name, index_name from user_indexes@&prex.lnk
where table_name in (select table_name from &prex.common_tables)
minus
select table_name, index_name from &prex.common_indexes;
PROMPT
PROMPT
PROMPT  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT INDEXES MISSING IN REMOTE SCHEMA FOR COMMON TABLES
PROMPT  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select table_name, index_name from user_indexes
where table_name in (select table_name from &prex.common_tables)
minus
select table_name, index_name from &prex.common_indexes;
PROMPT
PROMPT
PROMPT  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT COMMON INDEXES WITH DIFFERENT UNIQUENESS
PROMPT  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select a.table_name, a.index_name, a.uniqueness local, b.uniqueness remote
from user_indexes a,
         user_indexes@&prex.lnk b
where  a.index_name = b.index_name
and   a.uniqueness != b.uniqueness
and  (a.table_name, a.index_name) in
(select table_name, index_name from &prex.common_indexes);
PROMPT
PROMPT
PROMPT  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT INDEX COLUMNS MISSING IN THIS SCHEMA FOR COMMON INDEXES
PROMPT  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select index_name, column_name from user_ind_columns@&prex.lnk
where (table_name,index_name)  in 
(select table_name,index_name from &prex.common_indexes)
minus
select index_name, column_name from user_ind_columns
where (table_name,index_name)  in 
(select table_name,index_name from &prex.common_indexes);
PROMPT
PROMPT
PROMPT  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT INDEX COLUMNS MISSING IN REMOTE  SCHEMA FOR COMMON INDEXES
PROMPT  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select index_name, column_name from user_ind_columns
where (table_name,index_name)  in 
(select table_name,index_name from &prex.common_indexes)
minus
select index_name, column_name from user_ind_columns@&prex.lnk
where (table_name,index_name)  in 
(select table_name,index_name from &prex.common_indexes);
PROMPT
PROMPT
PROMPT  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT INDEX COLUMNS POSITIONED DIFFERENTLY FOR COMMON INDEXES
PROMPT  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select a.index_name, a.column_name, a.column_position local,
           b.column_position remote
           from user_ind_columns a,
                        user_ind_columns@&prex.lnk b
where  (a.table_name,a.index_name) in 
(select table_name,index_name from &prex.common_indexes) 
and b.index_name = a.index_name
and b.table_name = a.table_name
and a.column_name = b.column_name
and a.column_position != b.column_position;
 
PROMPT
PROMPT
PROMPT  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT CONSTRAINTS MISSING IN THIS SCHEMA FOR COMMON TABLES
PROMPT (WORKS ONLY FOR CONSTRAINT WITH NON SYSTEM GENERATED NAMES)
PROMPT  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select table_name,constraint_name from user_constraints@&prex.lnk
where constraint_name not like 'SYS%' and table_name in
(select table_name from &prex.common_tables)
minus
select table_name,constraint_name from user_constraints 
where constraint_name not like 'SYS%' and table_name in
(select table_name from &prex.common_tables);
PROMPT
PROMPT
PROMPT  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT CONSTRAINTS MISSING IN REMOTE SCHEMA FOR COMMON TABLES
PROMPT  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select table_name,constraint_name from user_constraints
where constraint_name not like 'SYS%' and table_name in
(select table_name from &prex.common_tables)
minus