This is not mine -- I just added the SYNONYMS
The remote link (for the database to compare to) is @IHP (global edit as
needed)
/*
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, 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.
This script has been tested on Oracle 7.3.
Added synonyms 6-14-01 T. Day
*/
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 DBA_objects@IHP where object_name like
chr(i)||chr(j)||chr(k)||'%'
AND OWNER = upper('&&1');
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 PAGESIZE 0
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 c:\project\listings\&&1.dbdiff.lst
PROMPT
PROMPT
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT SCHEMA DEFINITION DIFFERENCES &todaysdate
PROMPT
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT
PROMPT
--create database link EID1 connect to &REMOTESCHEMA identified
--by &REMOTEPASSW using '&CONNSTRING';
PROMPT
PROMPT
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT TABLES MISSING IN &&1 on IHP production:
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
column table_name format a38
column column_name format a38
column synonym_name format a38
column owner format a8
column constraint_name format a38
create table &prex.common_tables as
select table_name from DBA_TAbles@EID1 WHERE OWNER = upper('&&1')
intersect
select table_name from DBA_tables@IHP WHERE OWNER = upper('&&1');
select table_name from DBA_TAbles@EID1 WHERE OWNER = upper('&&1')
minus
select table_name from &prex.common_tables;
PROMPT
PROMPT
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT TABLES MISSING IN &&1 on EID production:
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select table_name from DBA_TAbles@IHP WHERE OWNER = upper('&&1')
minus
select table_name from &prex.common_tables;
PROMPT
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT SYNONYMS MISSING IN &&1 on IHP production:
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
create table &prex.common_synonyms as
select table_name, synonym_name, owner from DBA_synonyms@EID1 WHERE
table_owner = upper('&&1')
intersect
select table_name, synonym_name, owner from DBA_synonyms@IHP WHERE
table_owner = upper('&&1');
select table_name, synonym_name, owner from DBA_synonyms@EID1 WHERE
table_owner = upper('&&1')
minus
select table_name, synonym_name, owner from &prex.common_synonyms;
PROMPT
PROMPT
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT SYNONYMS MISSING IN &&1 on EID production:
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select table_name, synonym_name, owner from DBA_synonyms@IHP WHERE
table_owner = upper('&&1')
minus
select table_name, synonym_name, owner from &prex.common_synonyms;
PROMPT
PROMPT
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT COLUMNS MISSING IN &&1 on EID production FOR COMMON TABLES
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select table_name,column_name from DBA_tab_columns@EID1
where table_name in
(select table_name from &prex.common_tables) AND OWNER = upper('&&1')
minus
select table_name,column_name from DBA_tab_columns@IHP
where table_name in
(select table_name from &prex.common_tables) AND OWNER = upper('&&1');
PROMPT
PROMPT
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT COLUMNS MISSING IN &&1 on IHP production FOR COMMON TABLES
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select table_name,column_name from DBA_tab_columns@IHP
where table_name in
(select table_name from &prex.common_tables) AND OWNER = upper('&&1')
minus
select table_name,column_name from DBA_tab_columns@EID1
where table_name in
(select table_name from &prex.common_tables) AND OWNER = upper('&&1');
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 DBA_tab_columns@IHP l,&prex.common_tables c
where c.table_name=l.table_name AND OWNER = upper('&&1');
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 DBA_tab_columns@EID1 l,&prex.common_tables c
where c.table_name=l.table_name AND L.OWNER = upper('&&1');
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 EID_value, r.DATA_DEFAULT IHP_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 DBA_indexes@EID1
where table_name in (select table_name from &prex.common_tables) AND OWNER
= upper('&&1')
intersect
select table_name, INdex_name from DBA_indexes@IHP
where table_name in (select table_name from &prex.common_tables) AND OWNER
= upper('&&1');
PROMPT
PROMPT
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT INDEXES MISSING IN &&1 on EID production FOR COMMON TABLES
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
column index_name format a38
select table_name, index_name from DBA_indexes@EID1
where table_name in (select table_name from &prex.common_tables) AND OWNER
= upper('&&1')
minus
select table_name, index_name from &prex.common_indexes;
PROMPT
PROMPT
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT INDEXES MISSING IN &&1 on IHP production FOR COMMON TABLES
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select table_name, index_name from DBA_indexes@IHP
where table_name in (select table_name from &prex.common_tables) AND OWNER
= upper('&&1')
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 EID, b.uniqueness IHP
from DBA_indexes@IHP a,
DBA_indexes@EID1 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)
AND B.OWNER = upper('&&1')
AND A.OWNER = upper('&&1');
PROMPT
PROMPT
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT INDEX COLUMNS MISSING IN &&1 on EID production FOR COMMON INDEXES
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select index_name, column_name from DBA_ind_columns@EID1
where INDEX_OWNER = upper('&&1')
AND (table_name,index_name) in
(select table_name,index_name from &prex.common_indexes)
minus
select index_name, column_name from DBA_ind_columns@IHP
where INDEX_OWNER = upper('&&1')
AND (table_name,index_name) in
(select table_name,index_name from &prex.common_indexes);
PROMPT
PROMPT
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT INDEX COLUMNS MISSING IN &&1 on IHP production FOR COMMON INDEXES
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select index_name, column_name from DBA_ind_columns@IHP
where (table_name,index_name) in
(select table_name,index_name from &prex.common_indexes) AND INDEX_OWNER =
upper('&&1')
minus
select index_name, column_name from DBA_ind_columns@EID1
where (table_name,index_name) in
(select table_name,index_name from &prex.common_indexes) AND INDEX_OWNER =
upper('&&1');
PROMPT
PROMPT
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT INDEX COLUMNS POSITIONED DIFFERENTLY FOR COMMON INDEXES
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select a.index_name, a.column_name, a.column_position EID,
b.column_position IHP
from DBA_ind_columns@IHP a,
DBA_ind_columns@EID1 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
AND B.INDEX_OWNER = upper('&&1')
AND A.INDEX_OWNER = upper('&&1');
PROMPT
PROMPT
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT CONSTRAINTS MISSING IN &&1 on EID production FOR COMMON TABLES
PROMPT (WORKS ONLY FOR CONSTRAINT WITH NON SYSTEM GENERATED NAMES)
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select table_name,constraint_name from DBA_constraints@EID1
where constraint_name not like 'SYS%' and table_name in
(select table_name from &prex.common_tables) AND OWNER = upper('&&1')
minus
select table_name,constraint_name from DBA_constraints@IHP
where constraint_name not like 'SYS%' and table_name in
(select table_name from &prex.common_tables) AND OWNER = upper('&&1');
PROMPT
PROMPT
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT CONSTRAINTS MISSING IN &&1 on IHP production FOR COMMON TABLES
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select table_name,constraint_name from DBA_constraints@IHP
where constraint_name not like 'SYS%' and table_name in
(select table_name from &prex.common_tables)
AND OWNER = upper('&&1')
minus
select table_name,constraint_name from DBA_constraints@EID1
where constraint_name not like 'SYS%' and table_name in
(select table_name from &prex.common_tables)
AND OWNER = upper('&&1');
PROMPT
PROMPT
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT COMMON CONSTRAINTS, TYPE MISMATCH
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select a.constraint_name,a.constraint_type EID_type,
b.constraint_type IHP_type
from DBA_constraints@IHP a, DBA_constraints@EID1 b where
a.table_name = b.table_name and
a.constraint_name=b.constraint_name and
a.constraint_type !=b.constraint_type
AND B.OWNER = upper('&&1')
AND A.OWNER = upper('&&1');
PROMPT
PROMPT
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT COMMON CONSTRAINTS, TABLE MISMATCH
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select a.constraint_name,a.table_name,b.table_name from
DBA_constraints@IHP a, DBA_constraints@EID1 b,
(select z.constraint_name from
(select constraint_name, table_name from DBA_constraints@IHP WHERE OWNER =
upper('&&1') union
select constraint_name, table_name from DBA_constraints@EID1 WHERE OWNER =
upper('&&1')) z
group by constraint_name having count(*) >1) q
where a.constraint_name = q.constraint_name and
b.constraint_name=q.constraint_name
and a.table_name != b.table_name
AND B.OWNER = upper('&&1')
AND A.OWNER = upper('&&1');
create table &prex.comcons as
select constraint_name, constraint_type, table_name
from DBA_constraints@IHP WHERE OWNER = upper('&&1')
intersect
select constraint_name, constraint_type, table_name
from DBA_constraints@EID1 WHERE OWNER = upper('&&1');
delete from &prex.comcons where constraint_name in
(select constraint_name from &prex.comcons
group by constraint_name having count(*) > 1);
delete from &prex.comcons where constraint_name like 'SYS%';
PROMPT
PROMPT
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT DIFFERENCES IN COLUMN USAGE FOR CONSTRAINT DEFS
PROMPT (Unique key, Primary Key, Foreign key)
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
declare
cursor c1 is
select a.constraint_name,a.position,a.column_name,b.constraint_type
from DBA_cons_columns@IHP a, &prex.comcons b
where a.constraint_name=b.constraint_name
AND OWNER = upper('&&1')
union
select a.constraint_name,a.position,a.column_name,b.constraint_type
from DBA_cons_columns@EID1 a, &prex.comcons b
where a.constraint_name=b.constraint_name
AND A.OWNER = upper('&&1')
minus
(select a.constraint_name,a.position,a.column_name,b.constraint_type
from DBA_cons_columns@IHP a, &prex.comcons b
where a.constraint_name=b.constraint_name
AND OWNER = upper('&&1')
intersect
select a.constraint_name,a.position,a.column_name,b.constraint_type
from DBA_cons_columns@EID1 a, &prex.comcons b
where a.constraint_name=b.constraint_name
AND A.OWNER = upper('&&1')
);
i binary_integer;
begin
for c in c1 loop
dbms_output.put_line('COLUMN USAGE DIFFERENCE FOR '||c.constraint_type||
' CONSTRAINT '||c.constraint_name);
dbms_output.put_line('. Local columns:');
i:=1;
for c2 in (select column_name col
from DBA_cons_columns@IHP
where constraint_name=c.constraint_name AND OWNER = upper('
&&1') order by position)
loop
dbms_output.put_line('. '||c2.col);
end loop;
i:=1;
dbms_output.put_line('. Remote columns:');
for c3 in (select column_name col
from DBA_cons_columns@EID1
where constraint_name=c.constraint_name AND OWNER = upper('
&&1')
)
loop
dbms_output.put_line('. '||c3.col);
end loop;
end loop;
end;
/
PROMPT
PROMPT
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT DIFFERENCES IN CHECK CONSTRAINT DEFS
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--Remove two tables that have constraints too large to be parsed below
delete from &prex.comcons where table_name = 'ADDRESSES';
delete from &prex.comcons where table_name = 'PHONES';
set serveroutput on
declare
cursor c1 is select constraint_name,constraint_type,table_name
from &prex.comcons where constraint_type='C';
cons varchar2(50);
tab1 varchar2(50);
tab2 varchar2(50);
search1 varchar2(32000);
search2 varchar2(32000);
begin
dbms_output.enable(100000);
for c in c1 loop
select search_condition into search1 from DBA_constraints@EID1
where constraint_name=c.constraint_name AND OWNER = upper('&&1');
select search_condition into search2 from DBA_constraints@IHP
where constraint_name=c.constraint_name AND OWNER = upper('&&1');
if search1 != search2 then
dbms_output.put_line('Check constraint '||c.constraint_name||
' defined differently!');
dbms_output.put_line('. EID definition:');
dbms_output.put_line('. '||search1);
dbms_output.put_line('. IHP definition:');
dbms_output.put_line('. '||search2);
end if;
end loop;
end;
/
PROMPT
PROMPT
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT TRIGGERS MISSING IN &&1 on IHP production
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select trigger_name from DBA_Triggers@EID1 WHERE OWNER = upper('&&1') minus
select trigger_name from DBA_Triggers@IHP WHERE OWNER = upper('&&1');
PROMPT
PROMPT
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT TRIGGERS MISSING IN &&1 on EID production
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select trigger_name from DBA_Triggers@IHP WHERE OWNER = upper('&&1') minus
select trigger_name from DBA_Triggers@EID1 WHERE OWNER = upper('&&1');
PROMPT
PROMPT
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT TRIGGER DEFINITION DIFFERENCES ON COMMON TRIGGERS
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
set serveroutput on
declare
cursor c1 is select
TRIGGER_NAME,TRIGGER_TYPE,TRIGGERING_EVENT,
TABLE_NAME,REFERENCING_NAMES,rtrim(WHEN_CLAUSE,' '),STATUS,
rtrim(replace(description,'"&thisuser".',null),' ') DESCRIPTION,
TRIGGER_BODY from DBA_Triggers@IHP WHERE OWNER = upper('&&1');
nam1 varchar2(30);
type1 varchar2(16);
event1 varchar2(26);
table1 varchar2(30);
ref1 varchar2(87);
when1 varchar2(2000);
status1 varchar2(8);
desc1 varchar2(2000);
body1 varchar2(32000);
type2 varchar2(16);
event2 varchar2(26);
table2 varchar2(30);
ref2 varchar2(87);
when2 varchar2(2000);
status2 varchar2(8);
desc2 varchar2(2000);
body2 varchar2(32000);
pr_head boolean;
begin
dbms_output.enable(100000);
open c1;
loop
fetch c1 into nam1,type1,event1,table1,ref1,when1,status1,desc1,body1;
exit when c1%notfound;
begin
select
TRIGGER_TYPE,TRIGGERING_EVENT,
TABLE_NAME,REFERENCING_NAMES,rtrim(WHEN_CLAUSE,' '),STATUS,
rtrim(replace(description,upper('"&remoteschema".'),null),' ')
DESCRIPTION,
TRIGGER_BODY
into type2,event2,table2,ref2,when2,status2,desc2,body2
from DBA_Triggers@EID1
where trigger_name=nam1 AND OWNER = upper('&&1');
pr_head := FALSE;
if table1 != table2 then
dbms_output.put_line('T R I G G E R : '||nam1);
dbms_output.put_line('
-------------------------------------------------'||
'-----------------------');
pr_head := TRUE;
dbms_output.put_line(' ');
dbms_output.put_line('DEFINED ON DIFFERENT TABLES!');
dbms_output.put_line('. EID table_name : '||table1);
dbms_output.put_line('. IHP table_name: '||table2);
end if;
if event1 != event2 then
if not pr_head then
dbms_output.put_line('T R I G G E R : '||nam1);
dbms_output.put_line('
-------------------------------------------------'||
'-----------------------');
pr_head := TRUE;
end if;
dbms_output.put_line(' ');
dbms_output.put_line('DEFINED FOR DIFFERENT EVENTS!');
dbms_output.put_line('. EID event: '||event1);
dbms_output.put_line('. IHP event: '||event2);
end if;
if type1 != type2 then
if not pr_head then
dbms_output.put_line('T R I G G E R : '||nam1);
dbms_output.put_line('
-------------------------------------------------'||
'-----------------------');
pr_head := TRUE;
end if;
dbms_output.put_line(' ');
dbms_output.put_line('DIFFERENT TYPES!');
dbms_output.put_line('. EID type: '||type1);
dbms_output.put_line('. IHP type: '||type2);
end if;
if ref1 != ref2 then
if not pr_head then
dbms_output.put_line('T R I G G E R : '||nam1);
dbms_output.put_line('
-------------------------------------------------'||
'-----------------------');
pr_head := TRUE;
end if;
dbms_output.put_line(' ');
dbms_output.put_line('DIFFERENT REFERENCES!');
dbms_output.put_line('. EID ref: '||ref1);
dbms_output.put_line('. IHP ref: '||ref2);
end if;
if when1 != when2 then
dbms_output.put_line(' ');
if not pr_head then
dbms_output.put_line('T R I G G E R : '||nam1);
dbms_output.put_line('
-------------------------------------------------'||
'-----------------------');
pr_head := TRUE;
end if;
dbms_output.put_line('DIFFERENT WHEN CLAUSES!');
dbms_output.put_line('. EID when_clause:');
dbms_output.put_line(when1);
dbms_output.put_line('. IHP when_clause: ');
dbms_output.put_line(when2);
end if;
if status1 != status2 then
dbms_output.put_line(' ');
dbms_output.put_line('DIFFERENT STATUS!');
dbms_output.put_line('. EID status: '||status1);
dbms_output.put_line('. IHP status: '||status2);
end if;
if replace(desc1,chr(10),'') != replace(desc2,chr(10),'') then
dbms_output.put_line(' ');
dbms_output.put_line('DIFFERENT DESCRIPTIONS!');
dbms_output.put_line('EID definition: ');
dbms_output.put_line(desc1);
dbms_output.put_line('IHP definition: ');
dbms_output.put_line(desc2);
end if;
if body1 != body2 then
dbms_output.put_line(' ');
dbms_output.put_line('THE PL/SQL BLOCKS ARE DIFFERENT! ');
dbms_output.put_line(' ');
end if;
exception when NO_DATA_FOUND then null;
when others then raise_application_error(-20010,SQLERRM);
end;
end loop;
end;
/
PROMPT
PROMPT
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT MISSING PROCEDURES/PACKAGES/FUNCTIONS IN &&1 on IHP production
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select distinct name,type from DBA_source@IHP WHERE OWNER = upper('&&1')
minus
select distinct name,type from DBA_source@EID1 WHERE OWNER = upper('&&1');
PROMPT
PROMPT
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT MISSING PROCEDURES/PACKAGES/FUNCTIONS IN &&1 on EID production
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select distinct name,type from DBA_source@EID1 WHERE OWNER = upper('&&1')
minus
select distinct name,type from DBA_source@IHP WHERE OWNER = upper('&&1');
create table &prex.comcod as
select distinct name,type from DBA_source@IHP WHERE OWNER = upper('&&1')
intersect
select distinct name,type from DBA_source@EID1 WHERE OWNER = upper('&&1');
PROMPT
PROMPT
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT PROCEDURES/PACKAGES/FUNCTIONS WITH DIFFERENT DEFINITIONS
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select distinct q.name Object_name,q.type Object_type from
(select a.name,a.type,a.line,a.text
from DBA_source@IHP a, &prex.comcod b
where a.name=b.name AND OWNER = upper('&&1') union
select a.name,a.type,a.line,a.text
from DBA_source@EID1 a, &prex.comcod b
where a.name=b.name
AND A.OWNER = upper('&&1')
minus
(select a.name,a.type,a.line,a.text
from DBA_source@IHP a, &prex.comcod b
where a.name=b.name AND OWNER = upper('&&1')
intersect
select a.name,a.type,a.line,a.text
from DBA_source@EID1 a, &prex.comcod b
where a.name=b.name
AND OWNER = upper('&&1'))) q;
PROMPT
PROMPT
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT VIEWS missing in &&1 on IHP production:
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
create table &prex.common_views as
select view_name from DBA_views@EID1 WHERE OWNER = upper('&&1')
intersect
select view_name from DBA_views@IHP WHERE OWNER = upper('&&1');
select view_name from DBA_views@EID1 WHERE OWNER = upper('&&1')
minus
select view_name from &prex.common_views;
PROMPT
PROMPT
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT VIEWS missing in &&1 on EID production:
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select view_name from DBA_views@IHP WHERE OWNER = upper('&&1')
minus
select view_name from DBA_views@EID1 WHERE OWNER = upper('&&1');
PROMPT
PROMPT
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT VIEWS WITH DIFFERENCES IN THE DEFINITION
PROMPT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
declare
def1 varchar2(32000);
def2 varchar2(32000);
len1 number;
len2 number;
i number;
cursor c1 is select view_name from &prex.common_views;
begin
dbms_output.enable(200000);
for c in c1 loop
select text,text_length into def1,len1
from DBA_Views@IHP where view_name=c.view_name AND OWNER = upper
('&&1');
select text,text_length into def2,len2
from DBA_Views@EID1 where view_name=c.view_name AND OWNER = upper('
&&1');
i := 1;
def1:=replace(def1,' ','');
def2:=replace(def2,' ','');
if def1 != def2 or length(def1) != length(def2) then
dbms_output.put_line(lpad('-',35+length(c.view_name),'-'));
dbms_output.put_line('| '||c.view_name ||
' |');
dbms_output.put_line(lpad('-',35+length(c.view_name),'-'));
dbms_output.put_line('EID text_length: ' || to_char(len1));
dbms_output.put_line('IHP text_length): ' || to_char(len2));
dbms_output.put_line(' ');
i := 1;
while i <= length(def1) loop
if substr(def1,i,240) != substr(def2,i,240) then
dbms_output.put_line('Difference at offset ' ||
to_char(i)
);
dbms_output.put_line(' EID: ' ||
substr(def1,i,240));
dbms_output.put_line(' IHP: ' ||
substr(def2,i,240));
end if;
i := i + 240;
end loop;
end if;
if length(def2) > length(def1) then
dbms_output.put_line('IHP longer than EID. Next 255 bytes: ');
dbms_output.put_line(substr(def2,length(def1),255));
end if;
end loop;
end;
/
prompt ************** END of REPORT ************************
spool off
--drop database link test1;
drop table &prex.comcod;
drop table &prex.diff_cols1;
drop table &prex.diff_cols2;
drop table &prex.common_tables;
drop table &prex.common_views;
drop table &prex.common_indexes;
drop table &prex.common_synonyms;
drop table &prex.comcons;
set verify on
set feedback on
undef prex
undef prefx
undef a
undef thisuser
undef b
undef REMOTESCHEMA
undef REMOTEPASSW
undef connstring
undef c
undef todaysdate
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Thomas Day
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).
