-- schema_diff.sql
-- copyright Robert Perrine
-- check for compatible declarations betweeen two schemas
-- user is prompted for primary owner, secondary owners, and both links
--
-- REVISION HISTORY
-- DATE WHO DESCRIPTION
-- 050307 perrine add option 5 to bypass detailed source checks
-- 000922 perrine re-write
-- 000818 perrine add link to rowtypes for version inconsistencies
-- 960123 perrine changed SYS.V_$ tables to synonym V$ tables
-- 951108 perrine rewrites for speed optimization
-- 951106 perrine initial code for trigger comparison
-- 951102 perrine added user and instance names to heading
-- 951025 perrine initial code for function comparison
-- 951024 perrine initial code for index comparison
-- 951014 perrine converted output from screen to table
-- 951014 perrine split long main into separate procedures
-- 951011 perrine refined view parsing algorithms
-- 951007 perrine reworked to limit view to only relevant data
-- 950913 perrine added remote link capability
-- 950909 perrine converted code from pkg_oc2oe.sp_veri
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-- Set the environment
clear columns
clear computes
clear breaks
set serveroutput on size 1000000
set echo off
set feedback off
set heading on
set linesize 132
set pagesize 2000
set space 1
set tab off
set termout on
set timing off
set underline off
set verify off
set wrap on
column sortby noprint
column top heading '' format a12
column typ heading 'Type' format a12
column obj heading 'Object' format a55
column txt heading 'Description' format a63
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-- Prompt for the user selection
PROMPT The available links are:
SELECT db_link
FROM SYS.All_db_links
WHERE owner = USER
/
prompt .
PROMPT Parameter 1 is the OWNER of the first schema
PROMPT Parameter 2 is the DB-LINK to the first schema
prompt .
PROMPT Parameter 3 is the OWNER of the second schema
PROMPT Parameter 4 is the DB-LINK to the second schema
prompt .
PROMPT Parameter 5 is N to bypass detailed checks on source code
column own1 noprint new_value O1
column link1 noprint new_value L1
column own2 noprint new_value O2
column link2 noprint new_value L2
define O1='&1'
define L1='&2'
define O2='&3'
define L2='&4'
define NS='&5'
select upper('&O1') own1 from dual;
select '@'||upper('&L1') link1 from dual where '&L1' is not null;
select upper('&O2') own2 from dual;
select '@'||upper('&L2') link2 from dual where '&L2' is not null;
undefine 1
undefine 2
undefine 3
undefine 4
undefine 5
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
spool schema_diff.txt
select 'HEADER' top
from dual
/
select 1 sortby,
'Note' typ,
'Schema comparison' obj,
'Prepared on '||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI')||'.' txt
from dual
union
select 2 sortby,
'Note' typ,
'Prepared by' obj,
USER txt
from dual
union
select 3 sortby,
'Note' typ,
'Host database ' obj,
vd.name txt
from sys.v_$database vd
union
select 4 sortby,
'Note' typ,
'Schema_1' obj,
'&O1&L1' txt
from dual
union
select 5 sortby,
'Note' typ,
'Schema_2' obj,
'&O2&L2' txt
from dual
order by 1
/
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
select 'TABLES' top
from dual
/
select 'Table' typ,
t1.table_name obj,
'In schema 1 but not in 2.' txt
from sys.all_tables&L1 t1
where t1.owner = '&O1'
and not exists
( select 1
from sys.all_tables&L2 t2
where t2.owner = '&O2'
and t2.table_name = t1.table_name
)
order by 2
/
select 'Table' typ,
t1.table_name obj,
'In schema 2 but not in 1.' txt
from sys.all_tables&L2 t1
where t1.owner = '&O2'
and not exists
( select 1
from sys.all_tables&L1 t2
where t2.owner = '&O1'
and t2.table_name = t1.table_name
)
order by 2
/
select 'Column' typ,
rtrim(t1.table_name)||'.'||t1.column_name obj,
'In schema 1 but not in 2.' txt
from sys.all_tab_columns&L1 t1
where t1.owner = '&O1'
and t1.table_name in
( select t2.table_name
from sys.all_tables&L1 t2, sys.all_tables&L2 t4
where t2.owner = '&O1'
and t4.owner = '&O2'
and t4.table_name = t2.table_name
)
and not exists
( select 1
from sys.all_tab_columns&L2 t3
where t3.owner = '&O2'
and t3.table_name = t1.table_name
and t3.column_name = t1.column_name
)
order by 2
/
select 'Column' typ,
rtrim(t1.table_name)||'.'||t1.column_name obj,
'In schema 2 but not in 1.' txt
from sys.all_tab_columns&L2 t1
where t1.owner = '&O2'
and t1.table_name in
( select t2.table_name
from sys.all_tables&L1 t2, sys.all_tables&L2 t4
where t2.owner = '&O1'
and t4.owner = '&O2'
and t4.table_name = t2.table_name
)
and not exists
( select 1
from sys.all_tab_columns&L1 t3
where t3.owner = '&O1'
and t3.table_name = t1.table_name
and t3.column_name = t1.column_name
)
order by 2
/
select 'Column' typ,
rtrim(t1.table_name)||'.'||t1.column_name obj,
'Definitions are different.' txt
from sys.all_tab_columns&L1 t1, sys.all_tab_columns&L2 t2
where t1.owner = '&O1'
and t2.owner = '&O2'
and t1.table_name = t2.table_name
and t1.column_name = t2.column_name
and ( nvl(t1.data_type,'x') <> nvl(t2.data_type,'x')
or nvl(t1.data_length,0) <> nvl(t2.data_length,0)
or nvl(t1.data_precision,0) <> nvl(t2.data_precision,0)
or nvl(t1.data_scale,0) <> nvl(t2.data_scale,0)
or nvl(t1.nullable,'x') <> nvl(t2.nullable,'x')
)
and t1.table_name in
( select t3.table_name
from sys.all_tables&L1 t3
where t3.owner = '&O1'
)
order by 2
/
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
select 'INDEXES' top
from dual
/
select 'Index' typ,
rtrim(t1.table_name)||'.'||t1.index_name obj,
'In schema 1 but not in 2.' txt
from sys.all_indexes&L1 t1
where t1.owner = '&O1'
and not exists
( select 1
from sys.all_indexes&L2 t2
where t2.owner = '&O2'
and t2.index_name = t1.index_name
)
order by 2
/
select 'Index' typ,
rtrim(t1.table_name)||'.'||t1.index_name obj,
'In schema 2 but not in 1.' txt
from sys.all_indexes&L2 t1
where t1.owner = '&O2'
and not exists
( select 1
from sys.all_indexes&L1 t2
where t2.owner = '&O1'
and t2.index_name = t1.index_name
)
order by 2
/
select 'Index' typ,
t1.index_name obj,
'Applies to different tables.' txt
from sys.all_indexes&L1 t1, sys.all_indexes t2
where t1.owner = '&O1'
and t2.owner = '&O2'
and t2.index_name = t1.index_name
and t2.table_name <> t1.table_name
order by 2
/
select 'Index' typ,
rtrim(t1.table_name)||'.'||t1.index_name obj,
'Property declarations differ.' txt
from sys.all_indexes&L1 t1, sys.all_indexes t2
where t1.owner = '&O1'
and t2.owner = '&O2'
and t2.index_name = t1.index_name
and t2.table_name = t1.table_name
and ( nvl(t1.uniqueness,'x') <> nvl(t2.uniqueness,'x')
or nvl(t1.table_type,'x') <> nvl(t2.table_type,'x')
or nvl(t1.status,'x') <> nvl(t2.status,'x')
)
order by 2
/
select 'Column' typ,
rtrim(t1.table_name)||'.'||rtrim(t1.index_name)||'.'||t1.column_name obj,
'In schema 1 but not 2.' txt
from sys.all_ind_columns&L1 t1
where t1.index_owner = '&O1'
and (t1.table_name, t1.index_name) in
( select t2.table_name, t2.index_name
from sys.all_indexes&L1 t2, sys.all_indexes&L2 t4
where t2.owner = '&O1'
and t4.owner = '&O2'
and t4.index_name = t2.index_name
and t4.table_name = t2.table_name
)
and not exists
( select 1
from sys.all_ind_columns&L2 t3
where t3.index_owner = '&O2'
and t3.index_name = t1.index_name
and t3.table_name = t1.table_name
and t3.column_name = t1.column_name
)
order by 2
/
select 'Column' typ,
rtrim(t1.table_name)||'.'||rtrim(t1.index_name)||'.'||t1.column_name obj,
'In schema 2 but not 1.' txt
from sys.all_ind_columns&L2 t1
where t1.index_owner = '&O2'
and (t1.table_name, t1.index_name) in
( select t2.table_name, t2.index_name
from sys.all_indexes&L1 t2, sys.all_indexes&L2 t4
where t2.owner = '&O1'
and t4.owner = '&O2'
and t4.index_name = t2.index_name
and t4.table_name = t2.table_name
)
and not exists
( select 1
from sys.all_ind_columns&L1 t3
where t3.index_owner = '&O1'
and t3.index_name = t1.index_name
and t3.table_name = t1.table_name
and t3.column_name = t1.column_name
)
order by 2
/
select 'Column' typ,
rtrim(t1.table_name)||'.'||rtrim(t1.index_name)||'.'||t1.column_name obj,
'Ordered differently' txt
from sys.all_ind_columns&L1 t1, sys.all_ind_columns&L2 t2
where t1.index_owner = '&O1'
and t2.index_owner = '&O2'
and t2.index_name = t1.index_name
and t2.table_name = t1.table_name
and t2.column_name = t1.column_name
and t2.column_position <> t1.column_position
order by 2
/
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
select 'CONSTRAINTS' top
from dual
/
select 'Note' typ,
' ' obj,
'Only Named constraints can be properly compared.' txt
from dual
/
select 'Constraint' typ,
rtrim(t1.table_name)||'.'||t1.constraint_name obj,
'In schema 1 but not in 2.' txt
from sys.all_constraints&L1 t1
where t1.owner = '&O1'
and t1.constraint_name not like 'SYS_%'
and not exists
( select 1
from sys.all_constraints&L2 t2
where t2.owner = '&O2'
and t2.table_name = t1.table_name
and t2.constraint_name = t1.constraint_name
)
order by 2
/
select 'Constraint' typ,
rtrim(t1.table_name)||'.'||t1.constraint_name obj,
'In schema 2 but not in 1.' txt
from sys.all_constraints&L2 t1
where t1.owner = '&O2'
and t1.constraint_name not like 'SYS_%'
and not exists
( select 1
from sys.all_constraints&L1 t2
where t2.owner = '&O1'
and t2.table_name = t1.table_name
and t2.constraint_name = t1.constraint_name
)
order by 2
/
select 'Constraint' typ,
rtrim(t1.table_name)||'.'||t1.constraint_name obj,
'Properties differ.' txt
from sys.all_constraints&L1 t1, sys.all_constraints&L2 t2
where t1.owner = '&O1'
and t2.owner = '&O2'
and t2.table_name = t1.table_name
and t2.constraint_name = t1.constraint_name
and ( t2.constraint_type <> t1.constraint_type
or nvl(t2.status,'x') <> nvl(t1.status,'x')
or nvl(t2.delete_rule,'x') <> nvl(t1.delete_rule,'x')
)
order by 2
/
declare
cursor c_con is
select t1.constraint_name, t1.table_name
from sys.all_constraints&L1 t1, sys.all_constraints&L2 t2
where t1.owner = '&O1'
and t2.owner = '&O2'
and t2.table_name = t1.table_name
and t2.constraint_name = t1.constraint_name;
t1_search varchar2(30000);
t2_search varchar2(30000);
begin
for r_con in c_con loop
begin
select t1.search_condition
into t1_search
from sys.all_constraints&L1 t1
where t1.owner = '&O1'
and t1.constraint_name = r_con.constraint_name;
--
select t2.search_condition
into t2_search
from sys.all_constraints&L1 t2
where t2.owner = '&O1'
and t2.constraint_name = r_con.constraint_name;
--
if t1_search <> t2_search then
dbms_output.put_line(rpad('Constraint',12,' ')||' '
||rpad(rtrim(r_con.table_name)||r_con.constraint_name,55,' ')||' '
||'Search conditions differ.');
end if;
exception
when others then
dbms_output.put_line('error');
end;
end loop;
end;
/
select 'Constraint' typ,
rtrim(t1.table_name)||'.'||t1.constraint_name obj,
'Foreign Key mismatch.' txt
from sys.all_constraints&L1 t1, sys.all_constraints&L1 r1
where t1.owner = '&O1'
and t1.constraint_type = 'F'
and r1.owner = t1.r_owner
and r1.constraint_name = t1.r_constraint_name
and not exists
( select 1
from sys.all_constraints&L2 t2, sys.all_constraints&L2 r2
where t2.owner = '&O2'
and t2.constraint_type = 'F'
and r2.owner = t2.r_owner
and r2.constraint_name = t2.r_constraint_name
and t2.table_name = t1.table_name
and r2.table_name = r1.table_name
and ( t2.constraint_name = t1.constraint_name
or ( t2.constraint_name like 'SYS_%' and t1.constraint_name like 'SYS_%')
)
and ( r2.constraint_name = r1.constraint_name
or ( r2.constraint_name like 'SYS_%' and r1.constraint_name like 'SYS_%')
)
)
/
select 'Constraint' typ,
rtrim(t1.table_name)||'.'||t1.constraint_name obj,
'Foreign Key mismatch.' txt
from sys.all_constraints&L2 t1, sys.all_constraints&L2 r1
where t1.owner = '&O2'
and t1.constraint_type = 'F'
and r1.owner = t1.r_owner
and r1.constraint_name = t1.r_constraint_name
and not exists
( select 1
from sys.all_constraints&L1 t2, sys.all_constraints&L1 r2
where t2.owner = '&O1'
and t2.constraint_type = 'F'
and r2.owner = t2.r_owner
and r2.constraint_name = t2.r_constraint_name
and t2.table_name = t1.table_name
and r2.table_name = r1.table_name
and ( t2.constraint_name = t1.constraint_name
or ( t2.constraint_name like 'SYS_%' and t1.constraint_name like 'SYS_%')
)
and ( r2.constraint_name = r1.constraint_name
or ( r2.constraint_name like 'SYS_%' and r1.constraint_name like 'SYS_%')
)
)
/
select 'Column' typ,
rtrim(t1.table_name)||'.'||rtrim(t1.constraint_name)||'.'||t1.column_name obj,
'In schema 1 but not in 2.' txt
from sys.all_cons_columns&L1 t1
where t1.owner = '&O1'
and (t1.constraint_name, t1.table_name) in
( select t2.constraint_name, t2.table_name
from sys.all_constraints&L1 t2, sys.all_constraints&L2 t3
where t2.owner = '&O1'
and t3.owner = '&O2'
and t3.table_name = t2.table_name
and ( t3.constraint_name = t2.constraint_name
or ( t3.constraint_name like 'SYS_%' and t2.constraint_name like 'SYS_%')
)
and t3.constraint_type = t2.constraint_type
)
and not exists
( select 1
from sys.all_cons_columns&L2 t4
where t4.owner = '&O2'
and t4.table_name = t1.table_name
and ( t4.constraint_name = t1.constraint_name
or ( t4.constraint_name like 'SYS_%' and t1.constraint_name like 'SYS_%' )
)
and t4.column_name = t1.column_name
)
/
select 'Column' typ,
rtrim(t1.table_name)||'.'||rtrim(t1.constraint_name)||'.'||t1.column_name obj,
'In schema 1 but not in 2.' txt
from sys.all_cons_columns&L2 t1
where t1.owner = '&O2'
and (t1.constraint_name, t1.table_name) in
( select t2.constraint_name, t2.table_name
from sys.all_constraints&L2 t2, sys.all_constraints&L1 t3
where t2.owner = '&O2'
and t3.owner = '&O1'
and t3.table_name = t2.table_name
and ( t3.constraint_name = t2.constraint_name
or ( t3.constraint_name like 'SYS_%' and t2.constraint_name like 'SYS_%')
)
and t3.constraint_type = t2.constraint_type
)
and not exists
( select 1
from sys.all_cons_columns&L1 t4
where t4.owner = '&O1'
and t4.table_name = t1.table_name
and ( t4.constraint_name = t1.constraint_name
or ( t4.constraint_name like 'SYS_%' and t1.constraint_name like 'SYS_%' )
)
and t4.column_name = t1.column_name
)
/
select 'Column' typ,
rtrim(t1.table_name)||'.'||rtrim(t1.constraint_name)||'.'||t1.column_name obj,
'Positions differ.' txt
from sys.all_cons_columns&L1 t1, sys.all_cons_columns&L2 t2
where t1.owner = '&O1'
and t2.owner = '&O2'
and t2.constraint_name = t1.constraint_name
and t2.table_name = t1.table_name
and t2.column_name = t1.column_name
and t2.position <> t1.position
/
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
select 'SEQUENCES' top
from dual
/
select 'Sequence' typ,
t1.sequence_name obj,
'In schema 1 but not in 2.' txt
from sys.all_sequences&L1 t1
where t1.sequence_owner = '&O1'
and not exists
( select 1
from sys.all_sequences&L2 t2
where t2.sequence_owner = '&O2'
and t2.sequence_name = t1.sequence_name
)
order by 2
/
select 'Sequence' typ,
t1.sequence_name obj,
'In schema 2 but not in 1.' txt
from sys.all_sequences&L2 t1
where t1.sequence_owner = '&O2'
and not exists
( select 1
from sys.all_sequences&L1 t2
where t2.sequence_owner = '&O1'
and t2.sequence_name = t1.sequence_name
)
order by 2
/
select 'Sequence' typ,
t1.sequence_name obj,
'Properties differ.' txt
from sys.all_sequences&L1 t1, sys.all_sequences&L2 t2
where t1.sequence_owner = '&O1'
and t2.sequence_owner = '&O2'
and t2.sequence_name = t1.sequence_name
and ( nvl(t2.min_value,0) <> nvl(t1.min_value,0)
or nvl(t2.max_value,0) <> nvl(t1.max_value,0)
or t2.increment_by <> t1.increment_by
or nvl(t2.cycle_flag,'x') <> nvl(t1.cycle_flag,'x')
or t2.cache_size <> t1.cache_size
or nvl(t2.order_flag,'x') <> nvl(t1.order_flag,'x')
)
order by 2
/
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
select 'SOURCE' top
from dual
/
select DISTINCT t1.type typ,
t1.name obj,
'In schema 1 but not in 2.' txt
from sys.all_source&L1 t1
where t1.owner = '&O1'
and not exists
( select 1
from sys.all_source&L2 t2
where t2.owner = '&O2'
and t2.name = t1.name
and t2.type = t1.type
)
order by 2, 1
/
select DISTINCT t1.type typ,
t1.name obj,
'In schema 2 but not in 1.' txt
from sys.all_source&L2 t1
where t1.owner = '&O2'
and not exists
( select 1
from sys.all_source&L1 t2
where t2.owner = '&O1'
and t2.name = t1.name
and t2.type = t1.type
)
order by 2, 1
/
select t1.type typ,
t1.name obj,
'Line counts are '||ltrim(to_char(max(t1.line)))
||' vs '||ltrim(to_char(max(t2.line)))||'.' txt
from sys.all_source&L1 t1, sys.all_source&L2 t2
where nvl(upper(ltrim(rtrim('&NS'))),'y') != 'N'
and t1.owner ='&O1'
and t2.owner = '&O2'
and t2.name = t1.name
and t2.type = t1.type
group by t1.type, t1.name
having max(t1.line) <> max(t2.line)
order by 2, 1, 3
/
select t1.type typ,
t1.name obj,
'Differences starting on line '||ltrim(to_char(min(t1.line)))||'.' txt
from sys.all_source&L1 t1, sys.all_source&L2 t2
where nvl(upper(ltrim(rtrim('&NS'))),'y') != 'N'
and t1.owner ='&O1'
and t2.owner = '&O2'
and t2.name = t1.name
and t2.type = t1.type
and t2.line = t1.line
and ltrim(rtrim(t2.text)) <> ltrim(rtrim(t1.text))
group by t1.type, t1.name
order by 2, 1, 3
/
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
select 'TRIGGERS' top
from dual
/
select 'Trigger' typ,
rtrim(t1.table_name)||'.'||t1.trigger_name obj,
'In schema 1 but not in 2.' txt
from sys.all_triggers&L1 t1
where t1.owner = '&O1'
and not exists
( select 1
from sys.all_triggers&L2 t2
where t2.owner = '&O2'
and t2.trigger_name = t1.trigger_name
)
order by 2, 1
/
select 'Trigger' typ,
rtrim(t1.table_name)||'.'||t1.trigger_name obj,
'In schema 2 but not in 1.' txt
from sys.all_triggers&L2 t1
where t1.owner = '&O2'
and not exists
( select 1
from sys.all_triggers&L1 t2
where t2.owner = '&O1'
and t2.trigger_name = t1.trigger_name
)
order by 2, 1
/
select 'Trigger' typ,
rtrim(t1.table_name)||'.'||t1.trigger_name obj,
'Properties differ.' txt
from sys.all_triggers&L1 t1, sys.all_triggers&L2 t2
where t1.owner = '&O1'
and t2.owner = '&O2'
and t2.trigger_name = t1.trigger_name
and ( nvl(t2.trigger_type,'x') <> nvl(t1.trigger_type,'x')
or nvl(t2.triggering_event,'x') <> nvl(t1.triggering_event,'x')
or t2.table_name <> t1.table_name
or nvl(t2.referencing_names,'x') <> nvl(t1.referencing_names,'x')
or nvl(t2.status,'x') <> nvl(t1.status,'x')
or nvl(rtrim(t2.when_clause),'x') <> nvl(rtrim(t1.when_clause),'x')
or nvl(rtrim(translate(t2.description,chr(9)||chr(10)||chr(13),' ')),'x')
<> nvl(rtrim(translate(t1.description,chr(9)||chr(10)||chr(13),' ')),'x')
)
order by 2, 1
/
declare
cursor c_trg is
select t1.trigger_name, t1.table_name
from sys.all_triggers&L1 t1, sys.all_triggers&L2 t2
where t1.owner = '&O1'
and t2.owner = '&O2'
and t2.table_name = t1.table_name
and t2.trigger_name = t1.trigger_name;
t1_body varchar2(30000);
t2_body varchar2(30000);
begin
for r_trg in c_trg loop
begin
select t1.trigger_body
into t1_body
from sys.all_triggers&L1 t1
where t1.owner = '&O1'
and t1.trigger_name = r_trg.trigger_name;
--
select t2.trigger_body
into t2_body
from sys.all_triggers&L1 t2
where t2.owner = '&O1'
and t2.trigger_name = r_trg.trigger_name;
--
if t1_body <> t2_body then
dbms_output.put_line(rpad('Trigger',12,' ')||' '
||rpad(rtrim(r_trg.table_name)||r_trg.trigger_name,55,' ')||' '
||'Trigger Body differ.');
end if;
exception
when others then
dbms_output.put_line('error');
end;
end loop;
end;
/
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
select 'VIEWS' top
from dual
/
select 'View' typ,
rtrim(t1.view_name) obj,
'In schema 1 but not in 2.' txt
from sys.all_views&L1 t1
where t1.owner = '&O1'
and not exists
( select 1
from sys.all_views&L2 t2
where t2.owner = '&O2'
and t2.view_name = t1.view_name
)
order by 2, 1
/
select 'View' typ,
rtrim(t1.view_name) obj,
'In schema 2 but not in 1.' txt
from sys.all_views&L2 t1
where t1.owner = '&O2'
and not exists
( select 1
from sys.all_views&L1 t2
where t2.owner = '&O1'
and t2.view_name = t1.view_name)
order by 2, 1
/
declare
cursor c_view is
select t1.view_name
from sys.all_views&L1 t1, sys.all_views&L2 t2
where t1.owner = '&O1'
and t2.owner = '&O2'
and t2.view_name = t1.view_name;
t1_len number;
t2_len number;
t1_text varchar2(30000);
t2_text varchar2(30000);
begin
for r_view in c_view loop
begin
select t1.text_length, t1.text
into t1_len, t1_text
from sys.all_views&L1 t1
where t1.owner = '&O1'
and t1.view_name = r_view.view_name;
--
select t2.text_length, t2.text
into t2_len, t2_text
from sys.all_views&L2 t2
where t2.owner = '&O2'
and t2.view_name = r_view.view_name;
--
if nvl(t1_len,0) <> nvl(t2_len,0) then
dbms_output.put_line(rpad('View',12,' ')||' '
||rpad(r_view.view_name,55,' ')||' '
||'View text length differs '
||ltrim(to_char(t1_len))||' vs '||ltrim(to_char(t2_len)));
end if;
--
if nvl(t1_len,0) > 0 and nvl(t1_len,0) = nvl(t2_len,0)
and t1_text <> t2_text then
dbms_output.put_line(rpad('View',12,' ')||' '
||rpad(r_view.view_name,55,' ')||' '
||'View text differs.');
end if;
exception
when others then
dbms_output.put_line('error');
end;
end loop;
end;
/
select 'Column' typ,
rtrim(t1.table_name)||'.'||t1.column_name obj,
'In schema 1 but not in 2.' txt
from sys.all_tab_columns&L1 t1
where t1.owner = '&O1'
and t1.table_name in
( select t2.view_name
from sys.all_views&L1 t2, sys.all_views&L2 t4
where t2.owner = '&O1'
and t4.owner = '&O2'
and t4.view_name = t2.view_name
)
and not exists
( select 1
from sys.all_tab_columns&L2 t3
where t3.owner = '&O2'
and t3.table_name = t1.table_name
and t3.column_name = t1.column_name
)
order by 2
/
select 'Column' typ,
rtrim(t1.table_name)||'.'||t1.column_name obj,
'In schema 2 but not in 1.' txt
from sys.all_tab_columns&L2 t1
where t1.owner = '&O2'
and t1.table_name in
( select t2.view_name
from sys.all_views&L1 t2, sys.all_views&L2 t4
where t2.owner = '&O1'
and t4.owner = '&O2'
and t4.view_name = t2.view_name
)
and not exists
( select 1
from sys.all_tab_columns&L1 t3
where t3.owner = '&O1'
and t3.table_name = t1.table_name
and t3.column_name = t1.column_name
)
order by 2
/
select 'Column' typ,
rtrim(t1.table_name)||'.'||t1.column_name obj,
'Definitions are different.' txt
from sys.all_tab_columns&L1 t1, sys.all_tab_columns&L2 t2
where t1.owner = '&O1'
and t2.owner = '&O2'
and t1.table_name = t2.table_name
and t1.column_name = t2.column_name
and ( nvl(t1.data_type,'x') <> nvl(t2.data_type,'x')
or nvl(t1.data_length,0) <> nvl(t2.data_length,0)
or nvl(t1.data_precision,0) <> nvl(t2.data_precision,0)
or nvl(t1.data_scale,0) <> nvl(t2.data_scale,0)
or nvl(t1.nullable,'x') <> nvl(t2.nullable,'x')
)
and t1.table_name in
( select t3.view_name
from sys.all_views&L1 t3
where t3.owner = '&O1'
)
order by 2
/
spool off
-- end schema_diff.sql
|