Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Compare (diff) Oracle DB and MS-SQLServer DB - DBArtisan

RE: Compare (diff) Oracle DB and MS-SQLServer DB - DBArtisan

From: Thomas Day <tday6_at_csc.com>
Date: Fri, 26 Apr 2002 12:30:30 -0800
Message-ID: <F001.0045101F.20020426123030@fatcity.com> 






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_at_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_at_EID1 WHERE OWNER = upper('&&1')
intersect


select table_name from DBA_tables_at_IHP WHERE OWNER = upper('&&1');
select table_name from DBA_TAbles_at_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_at_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_at_EID1 WHERE
table_owner = upper('&&1')


intersect


select table_name, synonym_name, owner from DBA_synonyms_at_IHP WHERE
table_owner = upper('&&1');


select table_name, synonym_name, owner from DBA_synonyms_at_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_at_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_at_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_at_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_at_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_at_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_at_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_at_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_at_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_at_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_at_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_at_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_at_IHP a,


         DBA_indexes_at_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_at_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_at_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_at_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_at_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_at_IHP a,
                        DBA_ind_columns_at_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_at_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_at_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_at_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_at_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_at_IHP a, DBA_constraints_at_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_at_IHP a, DBA_constraints_at_EID1 b,
(select z.constraint_name from


(select constraint_name, table_name from DBA_constraints_at_IHP WHERE OWNER =
upper('&&1') union


select constraint_name, table_name from DBA_constraints_at_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_at_IHP WHERE OWNER = upper('&&1')
intersect


select constraint_name, constraint_type, table_name
from DBA_constraints_at_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_at_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_at_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_at_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_at_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_at_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_at_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_at_EID1
   where constraint_name=c.constraint_name AND OWNER = upper('&&1');
  select search_condition into search2 from DBA_constraints_at_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_at_EID1 WHERE OWNER = upper('&&1') minus
select trigger_name from DBA_Triggers_at_IHP WHERE OWNER = upper('&&1');
PROMPT



PROMPT



PROMPT





PROMPT TRIGGERS MISSING IN &&1 on EID production
PROMPT





select trigger_name from DBA_Triggers_at_IHP WHERE OWNER = upper('&&1') minus
select trigger_name from DBA_Triggers_at_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_at_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_at_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_at_IHP WHERE OWNER = upper('&&1')
minus


select distinct name,type from DBA_source_at_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_at_EID1 WHERE OWNER = upper('&&1')
minus


select distinct name,type from DBA_source_at_IHP WHERE OWNER = upper('&&1');
create table &prex.comcod as


select distinct name,type from DBA_source_at_IHP WHERE OWNER = upper('&&1')
intersect


select distinct name,type from DBA_source_at_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_at_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_at_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_at_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_at_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_at_EID1 WHERE OWNER = upper('&&1')
intersect


select view_name from DBA_views_at_IHP WHERE OWNER = upper('&&1');
select view_name from DBA_views_at_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_at_IHP WHERE OWNER = upper('&&1')
minus


select view_name from DBA_views_at_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_at_IHP where view_name=c.view_name AND OWNER = upper
('&&1');


  select text,text_length into def2,len2


      from DBA_Views_at_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: tday6_at_csc.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 Apr 26 2002 - 15:30:30 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US