Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Sriv.

Asked: October 22, 2000 - 10:18 pm UTC

Answered by: Tom Kyte - Last updated: June 02, 2011 - 9:08 am UTC

Category: Database - Version: 8.1.6

Viewed 10K+ times! This question is

You Asked

Tom

Would like to know if there is any script to compare schema's across 2 database, I would want to compare the table name, column name, data type and also the size, if there is any difference, i would want the script to display the db name, table name , field name and data type and length to be displayed.

There can be fields which is in table a of schema a and not in table a of schema b, there can be new fields either ways, or there can be spelling mistakes in field name. The length can change or datatype can be a variant.

Thanks

Sriv




and we said...


Not that I am aware of -- perhaps something like TOAD would have something.

It would not be hard to do. For example, to find all tables in database A not in B

select * from user_tables
minus
select * from user_tables@b;

To find all tables in database B not in A, just reverse the above.

To diff the tables -- to find column mismatches would be very similar:

for x in ( select a.table_name from user_tables a,
user_tables@b b
where a.table_name = b.table_name )
loop
select column_name, datatype, ... from user_tab_columns
where table_name = x.table_name
MINUS
select column_name, datatype, ... from user_tab_columns@b
where table_name = x.table_name
...
end loop;




and you rated our response

  (24 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

April 18, 2002 - 11:19 am UTC

Reviewer: A reader

Hi tom,

IF we do like this

To find all tables in database A not
in B

select table_name,column_name,column_id from all_tab_columns
where owner = 'PUBS'
MINUS
select table_name,column_name,column_id from all_tab_columns@b
where owner = 'PUBS'


all tables in database B not in A

select table_name,column_name,column_id from all_tab_columns
where owner = 'PUBS'
MINUS
select table_name,column_name,column_id from all_tab_columns@a
where owner = 'PUBS'

will it do for me ?




Tom Kyte

Followup  

April 18, 2002 - 12:17 pm UTC

sure, thats pretty much what I answered with more or less.

Script for comparing schemas "compare_schemas.sql"

April 18, 2002 - 12:49 pm UTC

Reviewer: Logan Palanisamy from Santa Clara, CA USA

Hello,

I use the following script to compare two schemas. The only restriction is that they have to be in the same database. What I normally do when I want to compare schmeas in two different database is to do a schema level exp/imp of one of the schemas and have them both in the same db.

May be you can modify the script to compare them directly across DBs.

============== The Script Starts Here ===============
-- compare_schemas.sql

set termout on
set feedback on
accept schma1 prompt 'Enter the name of the first schema :'
accept schma2 prompt 'Enter the name of the second schema :'
col s1 noprint new_value SCHEMA1
col s2 noprint new_value SCHEMA2
select upper('&&schma1') s1, upper('&&schma2') s2 from dual;

set linesize 80
set pagesize 60
set numformat 9,999,999,999
set colsep ' '
set trimspool on
set verify off
set feedback off
set pause off
set newpage 1
set termout off

clear breaks
clear buffer
clear columns


column TODAY noprint new_value _DATE
column server noprint new_value _SERVER
column dbname noprint new_value _SID
col cyymmdd new_value yymmdd noprint

select name dbname, to_char(SYSDATE, 'Mon DD, YYYY HH:MI AM') TODAY,
to_char(sysdate, 'yymmdd') cyymmdd
from sys.v_$database;

SELECT UPPER(MACHINE) server
from v$session
where program like '%PMON%';

spool /tmp/compare_schemas.lst
set heading off
select 'Column C1 refers to: ' || '&&SCHEMA1' ||CHR(10) ||
'Column C2 refers to: ' || '&&SCHEMA2' from dual;

set heading on
set colsep ''
ttitle left _DATE right 'Page: ' format 99 SQL.PNO skip 1 -
left 'Server: ' _SERVER -
RIGHT 'SID: ' _SID SKIP 1 -
center 'Object Information' skip 2
col owner format a10
col tbls format 9999
col indx format 9999
col vews format 9999
col trgr format 9999
col func format 9999
col proc format 9999
col pkgs format 9999
col pkgb format 9999
col sequ format 9999
col syno format 9999
col dblnk format 99999
col clstr format 99999


select owner,
sum(decode(object_type, 'TABLE', 1, 0)) Tbls,
sum(decode(object_type, 'INDEX', 1, 0)) Indx,
sum(decode(object_type, 'VIEW', 1, 0)) Vews,
sum(decode(object_type, 'TRIGGER', 1, 0)) Trgr,
sum(decode(object_type, 'FUNCTION', 1, 0)) Func,
sum(decode(object_type, 'PROCEDURE', 1, 0)) Proc,
sum(decode(object_type, 'PACKAGE', 1, 0)) Pkgs,
sum(decode(object_type, 'PACKAGE BODY', 1, 0)) Pkgb,
sum(decode(object_type, 'SEQUENCE', 1, 0)) Sequ,
sum(decode(object_type, 'SYNONYM', 1, 0)) Syno,
sum(decode(object_type, 'DATABASE LINK', 1, 0)) Dblnk,
sum(decode(object_type, 'CLUSTER', 1, 0)) Clstr
from dba_objects
where owner in ('&&SCHEMA1', '&&SCHEMA2')
GROUP BY owner;

set colsep ' '
set numformat 99,999,999
ttitle left _DATE right 'Page: ' format 99 SQL.PNO skip 1 -
left 'Server: ' _SERVER -
RIGHT 'SID: ' _SID skip 1 -
center 'Table and Index Info By Owner' skip 2

col total_cnt format 99,999 heading "Total Cnt"
col tables format 99,999 heading "Tables"
col indexes format 99,999 heading "Indexes"
col table_size heading "Tbl_Size(Kb)"
col Index_size heading "Idx_Size(Kb)"
col total_size heading "Total_Size(Kb)"

select owner,
SUM(decode(segment_type, 'TABLE', 1, 0)) Tables,
SUM(decode(segment_type, 'TABLE', bytes, 0))/1024 Table_Size,
SUM(decode(segment_type, 'INDEX', 1, 0)) Indexes,
SUM(decode(segment_type, 'INDEX', bytes, 0))/1024 Index_Size,
count(*) Total_cnt,
sum(bytes)/1024 Total_size
from dba_segments
where segment_type IN ('TABLE', 'INDEX')
and owner in ('&&SCHEMA1', '&&SCHEMA2')
GROUP BY owner;



ttitle left _DATE right 'Page: ' format 99 SQL.PNO skip 1 -
left 'Server: ' _SERVER -
RIGHT 'SID: ' _SID skip 1 -
center 'Mismatch in the Number of Columns' skip 2
col C1 format 999
col C2 format 999
col table_name format a25
select table_name, C1, C2
from (
select table_name,
max(decode(owner, '&&SCHEMA1', column_id, NULL)) C1,
max(decode(owner, '&&SCHEMA2', column_id, NULL)) C2
from dba_tab_columns
where owner in ('&&SCHEMA1', '&&SCHEMA2')
GROUP BY table_name) a
where a.C1 <> a.C2
or a.C1 is null
or a.C2 is null
/

ttitle left _DATE right 'Page: ' format 99 SQL.PNO skip 1 -
left 'Server: ' _SERVER -
RIGHT 'SID: ' _SID skip 1 -
center 'Column Mismatch' skip 2
col C1 format 99
col C2 format 99
col column_name format a25
select table_name, column_name, C1, C2
from (
select table_name, column_name,
max(decode(owner, '&&SCHEMA1', column_id, NULL)) C1,
max(decode(owner, '&&SCHEMA2', column_id, NULL)) C2
from dba_tab_columns
where owner in ('&&SCHEMA1', '&&SCHEMA2')
GROUP BY table_name, column_name) a
where a.C1 <> a.C2
or a.C1 is null
or a.C2 is null
order by table_name, c1, c2
/

-- Compare data type mismatch
ttitle left _DATE right 'Page: ' format 99 SQL.PNO skip 1 -
left 'Server: ' _SERVER -
RIGHT 'SID: ' _SID skip 1 -
center 'Data Type Mismatch' skip 2
col c1 format a15
col c2 format a15
col column_name format a20
col table_name format a20
select table_name, column_name, C1, C2
from (
select tc.table_name, tc.column_name,
max(decode(tc.owner, '&&SCHEMA1', tc.data_type || '(' || decode(tc.data_type, 'NUMBER', tc.data_precision || ',' || tc.data_scale, ,
max(decode(tc.owner, '&&SCHEMA2', tc.data_type || '(' || decode(tc.data_type, 'NUMBER', tc.data_precision || ',' || tc.data_scale, 2
from dba_tab_columns tc, dba_tables t
where tc.owner in ('&&SCHEMA1', '&&SCHEMA2')
and t.owner in ('&&SCHEMA1', '&&SCHEMA2')
and tc.table_name = t.table_name
GROUP BY tc.table_name, tc.column_name) a
where a.C1 <> a.C2
order by table_name, c1, c2
/

ttitle left _DATE right 'Page: ' format 99 SQL.PNO skip 1 -
left 'Server: ' _SERVER -
RIGHT 'SID: ' _SID skip 1 -
center 'Trigger Status Mismatch' skip 2
col trigger_name format a25
col C1 format a2
col C2 format a2
select trigger_name, trigger_type, C1, C2
from (select trigger_name, trigger_type,
max(decode(owner, '&&SCHEMA1', decode(status, 'ENABLED', 'E', 'DISABLED', 'D', NULL), NULL)) C1,
max(decode(owner, '&&SCHEMA2', decode(status, 'ENABLED', 'E', 'DISABLED', 'D', NULL), NULL)) C2
from dba_triggers
where owner in ('&&SCHEMA1', '&&SCHEMA2')
GROUP BY trigger_name, trigger_type) a
where a.C1 <> a.C2
or a.C1 is null
or a.C2 is null
/

ttitle left _DATE right 'Page: ' format 99 SQL.PNO skip 1 -
left 'Server: ' _SERVER -
RIGHT 'SID: ' _SID skip 1 -
center 'Mismatch in Stored Procedure Size (Num of Lines)' skip 2
col name format a25
col type format a15
col C1 format 9999
col C2 format 9999
select name, type, C1, C2
from (select name, type,
max(decode(owner, '&&SCHEMA1', line, NULL)) C1,
max(decode(owner, '&&SCHEMA2', line, NULL)) C2
from dba_source
where owner in ('&&SCHEMA1', '&&SCHEMA2')
GROUP BY name, type) a
where a.C1 <> a.C2
or a.C1 is null
or a.C2 is null
/

ttitle left _DATE right 'Page: ' format 99 SQL.PNO skip 1 -
left 'Server: ' _SERVER -
RIGHT 'SID: ' _SID skip 1 -
center 'Mismatch in Sequences' skip 2
col Seq_Name format a20
col min_c1 format 9999
col min_c2 format 9999
col max_c1 format 999,999,999
col max_c2 format 999,999,999
col inc_c1 format 9999
col inc_c2 format 9999
select sequence_name seq_name, min_c1, min_c2, max_c1, max_c2, inc_c1, inc_c2
from (select sequence_name,
max(decode(sequence_owner, '&&SCHEMA1', min_value, NULL)) min_c1,
max(decode(sequence_owner, '&&SCHEMA2', min_value, NULL)) min_c2,
max(decode(sequence_owner, '&&SCHEMA1', max_value, NULL)) max_c1,
max(decode(sequence_owner, '&&SCHEMA2', max_value, NULL)) max_c2,
max(decode(sequence_owner, '&&SCHEMA1', increment_by, NULL)) inc_c1,
max(decode(sequence_owner, '&&SCHEMA2', increment_by, NULL)) inc_c2
from dba_sequences
where sequence_owner in ('&&SCHEMA1', '&&SCHEMA2')
GROUP BY sequence_name) a
where a.min_c1 <> a.min_c2
or a.min_c1 is null
or a.min_c2 is null
or a.max_c1 <> a.max_c2
or a.max_c1 is null
or a.max_c2 is null
or a.inc_c1 <> a.inc_c2
or a.inc_c1 is null
or a.inc_c2 is null
/

ttitle left _DATE right 'Page: ' format 99 SQL.PNO skip 1 -
left 'Server: ' _SERVER -
RIGHT 'SID: ' _SID skip 1 -
center 'Count of ALL the Constraints' skip 2
col owner format a10
select owner,
sum(decode(constraint_type, 'P', 1, 0)) Primary_Key,
sum(decode(constraint_type, 'R', 1, 0)) Foreign_key,
sum(decode(constraint_type, 'U', 1, 0)) Unique_key,
sum(decode(constraint_type, 'C', 1, 0)) Check_Constraint
from dba_constraints
where owner in ('&&SCHEMA1', '&&SCHEMA2')
GROUP BY owner;

ttitle left _DATE right 'Page: ' format 99 SQL.PNO skip 1 -
left 'Server: ' _SERVER -
RIGHT 'SID: ' _SID skip 1 -
center 'Count of all the Enabled Constraints' skip 2
select owner,
sum(decode(constraint_type, 'P', 1, 0)) Primary_Key,
sum(decode(constraint_type, 'R', 1, 0)) Foreign_key,
sum(decode(constraint_type, 'U', 1, 0)) Unique_key,
sum(decode(constraint_type, 'C', 1, 0)) Check_Constraint
from dba_constraints
where owner in ('&&SCHEMA1', '&&SCHEMA2')
and status = 'ENABLED'
GROUP BY owner;

ttitle left _DATE right 'Page: ' format 99 SQL.PNO skip 1 -
left 'Server: ' _SERVER -
RIGHT 'SID: ' _SID skip 1 -
center 'Count of all the Disabled Constraints' skip 2
select owner,
sum(decode(constraint_type, 'P', 1, 0)) Primary_Key,
sum(decode(constraint_type, 'R', 1, 0)) Foreign_key,
sum(decode(constraint_type, 'U', 1, 0)) Unique_key,
sum(decode(constraint_type, 'C', 1, 0)) Check_Constraint
from dba_constraints
where owner in ('&&SCHEMA1', '&&SCHEMA2')
and status = 'DISABLED'
GROUP BY owner;

ttitle left _DATE right 'Page: ' format 99 SQL.PNO skip 1 -
left 'Server: ' _SERVER -
RIGHT 'SID: ' _SID skip 1 -
center 'Constraint Status Mismatch' skip 2
col constraint_name format a25
col C1 format a2
col C2 format a2
col constraint_type heading Type
select constraint_name, constraint_type, C1, C2
from (select constraint_name, constraint_type,
max(decode(owner, '&&SCHEMA1', decode(status, 'ENABLED', 'E', 'DISABLED', 'D', NULL), NULL)) C1,
max(decode(owner, '&&SCHEMA2', decode(status, 'ENABLED', 'E', 'DISABLED', 'D', NULL), NULL)) C2
from dba_constraints
where owner in ('&&SCHEMA1', '&&SCHEMA2')
and constraint_name not like 'SYS%'
GROUP BY constraint_name, constraint_type
order by constraint_type) a
where a.C1 <> a.C2
or a.C1 is null
or a.C2 is null
/

ttitle left _DATE right 'Page: ' format 99 SQL.PNO skip 1 -
left 'Server: ' _SERVER -
RIGHT 'SID: ' _SID skip 1 -
center 'Constraint Column Mismatch' skip 2
col column_name format a20
col C1 format 9
col C2 format 9
-- break on constraint_name noduplicates
select constraint_name, column_name, C1, C2
from (
select constraint_name, column_name,
max(decode(owner, '&&SCHEMA1', position, NULL)) C1,
max(decode(owner, '&&SCHEMA2', position, NULL)) C2
from dba_cons_columns
where owner in ('&&SCHEMA1', '&&SCHEMA2')
and constraint_name not like 'SYS%'
and position is not null
GROUP BY constraint_name, column_name) a
where a.C1 <> a.C2
or a.C1 is null
or a.C2 is null
order by constraint_name, c1, c2
/

ttitle left _DATE right 'Page: ' format 99 SQL.PNO skip 1 -
left 'Server: ' _SERVER -
RIGHT 'SID: ' _SID skip 1 -
center 'Index Column Mismatch on others tables' skip 2

col idx_name format a25
col tbl_name format a25
col column_name format a20
col ordr format 999 heading order

select index_owner || '.' || index_name idx_name,
table_owner || '.' || table_name tbl_name, column_name,
column_position ordr
from dba_ind_columns
where (index_owner in ('&&SCHEMA1', '&&SCHEMA2')
or table_owner in ('&&SCHEMA1', '&&SCHEMA2'))
and index_owner <> table_owner
order by index_name, index_owner, column_position
/

ttitle left _DATE right 'Page: ' format 99 SQL.PNO skip 1 -
left 'Server: ' _SERVER -
RIGHT 'SID: ' _SID skip 1 -
center 'Index Column Mismatch on ones own tables' skip 2
select index_name, table_name, column_name, C1, C2
from (
select index_name, table_name, column_name,
max(decode(index_owner, '&&SCHEMA1', column_position, NULL)) C1,
max(decode(index_owner, '&&SCHEMA2', column_position, NULL)) C2
from dba_ind_columns
where index_owner in ('&&SCHEMA1', '&&SCHEMA2')
and column_position is not null
and index_owner = table_owner
GROUP BY index_name, table_name, column_name) a
where a.C1 <> a.C2
or a.C1 is null
or a.C2 is null
order by index_name, c1, c2
/

ttitle left _DATE right 'Page: ' format 99 SQL.PNO skip 1 -
left 'Server: ' _SERVER -
RIGHT 'SID: ' _SID skip 1 -
center 'Mismatch in the number of Synonyms' skip 2
col C1 format 999
col C2 format 999
col table_owner format a25
select table_owner, C1, C2
from (
select table_owner,
sum(decode(owner, '&&SCHEMA1', 1, NULL)) C1,
sum(decode(owner, '&&SCHEMA2', 1, NULL)) C2
from dba_synonyms
where owner in ('&&SCHEMA1', '&&SCHEMA2')
GROUP BY table_owner) a
where a.C1 <> a.C2
or a.C1 is null
or a.C2 is null
/

ttitle left _DATE right 'Page: ' format 99 SQL.PNO skip 1 -
left 'Server: ' _SERVER -
RIGHT 'SID: ' _SID skip 1 -
center 'Mismatch in the Privileges granted to others - Sorted By Grantee' skip 2
col C1 format 999
col C2 format 999
col grantee format a25
select grantee, C1, C2
from (
select grantee,
sum(decode(grantor, '&&SCHEMA1', 1, NULL)) C1,
sum(decode(grantor, '&&SCHEMA2', 1, NULL)) C2
from dba_tab_privs
where owner in ('&&SCHEMA1', '&&SCHEMA2')
GROUP BY grantee) a
where a.C1 <> a.C2
or a.C1 is null
or a.C2 is null
/

ttitle left _DATE right 'Page: ' format 99 SQL.PNO skip 1 -
left 'Server: ' _SERVER -
RIGHT 'SID: ' _SID skip 1 -
center 'Mismatch in the Privileges granted to others - Sorted By Privilege' skip 2
col C1 format 999
col C2 format 999
col privilege format a25
select privilege, C1, C2
from (
select privilege,
sum(decode(grantor, '&&SCHEMA1', 1, NULL)) C1,
sum(decode(grantor, '&&SCHEMA2', 1, NULL)) C2
from dba_tab_privs
where owner in ('&&SCHEMA1', '&&SCHEMA2')
GROUP BY privilege) a
where a.C1 <> a.C2
or a.C1 is null
or a.C2 is null
/


ttitle left _DATE right 'Page: ' format 99 SQL.PNO skip 1 -
left 'Server: ' _SERVER -
RIGHT 'SID: ' _SID skip 1 -
center 'Mismatch in the Privileges Received from others - Sorted By Grantor' skip 2
col C1 format 999
col C2 format 999
col grantor format a25
select grantor, C1, C2
from (
select grantor,
sum(decode(grantee, '&&SCHEMA1', 1, NULL)) C1,
sum(decode(grantee, '&&SCHEMA2', 1, NULL)) C2
from dba_tab_privs
where grantee in ('&&SCHEMA1', '&&SCHEMA2')
GROUP BY grantor) a
where a.C1 <> a.C2
or a.C1 is null
or a.C2 is null
/

ttitle left _DATE right 'Page: ' format 99 SQL.PNO skip 1 -
left 'Server: ' _SERVER -
RIGHT 'SID: ' _SID skip 1 -
center 'Mismatch in the Privileges Received from others - Sorted By Privilege' skip 2
col C1 format 999
col C2 format 999
col privilege format a25
select privilege, C1, C2
from (
select privilege,
sum(decode(grantee, '&&SCHEMA1', 1, NULL)) C1,
sum(decode(grantee, '&&SCHEMA2', 1, NULL)) C2
from dba_tab_privs
where grantee in ('&&SCHEMA1', '&&SCHEMA2')
GROUP BY privilege) a
where a.C1 <> a.C2
or a.C1 is null
or a.C2 is null
/

ttitle left _DATE right 'Page: ' format 99 SQL.PNO skip 1 -
left 'Server: ' _SERVER -
RIGHT 'SID: ' _SID skip 1 -
center 'Mismatch in the Granted Roles' skip 2
col C1 format a2
col C2 format a2
col granted_role format a25
select granted_role, C1, C2
from (
select granted_role,
max(decode(grantee, '&&SCHEMA1', 'Y', NULL)) C1,
max(decode(grantee, '&&SCHEMA2', 'Y', NULL)) C2
from dba_role_privs
where grantee in ('&&SCHEMA1', '&&SCHEMA2')
GROUP BY granted_role) a
where a.C1 <> a.C2
or a.C1 is null
or a.C2 is null
/
ttitle left _DATE right 'Page: ' format 99 SQL.PNO skip 1 -
left 'Server: ' _SERVER -
RIGHT 'SID: ' _SID skip 1 -
center 'Mismatch in the System Privileges Received' skip 2
col C1 format a2
col C2 format a2
col privilege format a25
select privilege, C1, C2
from (
select privilege,
max(decode(grantee, '&&SCHEMA1', 'Y', NULL)) C1,
max(decode(grantee, '&&SCHEMA2', 'Y', NULL)) C2
from dba_sys_privs
where grantee in ('&&SCHEMA1', '&&SCHEMA2')
GROUP BY privilege) a
where a.C1 <> a.C2
or a.C1 is null
or a.C2 is null
/
clear buffer
clear columns
clear breaks
clear computes
ttitle off
btitle off
undefine SCHEMA1
undefine SCHEMA2

spool off



Wow...I figured you'd recommend an Oracle product

April 18, 2002 - 3:13 pm UTC

Reviewer: Daryl from Carrollton, TX USA

The Oracle Change Management Pack can do all this. It
looks not only at tables, indexes, views, etc., but
also at PL/SQL (and probably Java) in the database, too.
The real trick with CMP is to tell it to ignore things like
storage parameters (schema 1 has x extents; schema 2 has
y extents; CMP will flag it as different). CMP will also
generate the SQL needed to bring the two schemas into sync.

Tom Kyte

Followup  

April 18, 2002 - 9:12 pm UTC

well, thats a GUI and all -- I guess I never think about that part ;)

See this URL...

April 18, 2002 - 4:50 pm UTC

Reviewer: Andrew from ca, usa

</code> http://www.oracle.com/oramag/code/tips1997/index.html?cod07077.html <code>
Fix the typo under "TRIGGERS MISSING IN THIS SCHEMA" section where both tables in the query reference the same schema.

Script for comparing schemas "SchemaDiff.sql"

April 19, 2002 - 11:06 am UTC

Reviewer: BroadVision RCHU from Houston TX USA

I'm using this one.

/*
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
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.
*/

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
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
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);
PROMPT
PROMPT
PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT COMMON CONSTRAINTS, TYPE MISMATCH
PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select a.constraint_name,a.constraint_type local_type,
b.constraint_type remote_type
from user_constraints a, user_constraints@&prex.lnk b where
a.table_name = b.table_name and
a.constraint_name=b.constraint_name and
a.constraint_type !=b.constraint_type;
PROMPT
PROMPT
PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT COMMON CONSTRAINTS, TABLE MISMATCH
PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select a.constraint_name,a.table_name,b.table_name from
user_constraints a, user_constraints@&prex.lnk b,
(select z.constraint_name from
(select constraint_name, table_name from useR_constraints union
select constraint_name, table_name from user_constraints@&prex.lnk) 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;
create table &prex.comcons as
select constraint_name, constraint_type, table_name
from useR_constraints
intersect
select constraint_name, constraint_type, table_name
from user_constraints@&prex.lnk;
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 user_cons_columns a, &prex.comcons b
where a.constraint_name=b.constraint_name
union
select a.constraint_name,a.position,a.column_name,b.constraint_type
from user_cons_columns@&prex.lnk a, &prex.comcons b
where a.constraint_name=b.constraint_name
minus
(select a.constraint_name,a.position,a.column_name,b.constraint_type
from user_cons_columns a, &prex.comcons b
where a.constraint_name=b.constraint_name
intersect
select a.constraint_name,a.position,a.column_name,b.constraint_type
from user_cons_columns@&prex.lnk a, &prex.comcons b
where a.constraint_name=b.constraint_name
);
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 user_cons_columns
where constraint_name=c.constraint_name 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 user_cons_columns@&prex.lnk
where constraint_name=c.constraint_name
)
loop
dbms_output.put_line('. '||c3.col);
end loop;
end loop;
end;
/
PROMPT
PROMPT
PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT DIFFERENCES IN CHECK CONSTRAINT DEFS
PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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 user_constraints
where constraint_name=c.constraint_name;
select search_condition into search2 from user_constraints@&prex.lnk
where constraint_name=c.constraint_name;
if search1 != search2 then
dbms_output.put_line('Check constraint '||c.constraint_name||
' defined differently!');
dbms_output.put_line('. Local definition:');
dbms_output.put_line('. '||search1);
dbms_output.put_line('. Remote definition:');
dbms_output.put_line('. '||search2);
end if;
end loop;
end;
/
PROMPT
PROMPT
PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT TRIGGERS MISSING IN REMOTE SCHEMA
PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select trigger_name from user_Triggers minus
select trigger_name from user_Triggers@&prex.lnk;
PROMPT
PROMPT
PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT TRIGGERS MISSING IN THIS SCHEMA
PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select trigger_name from user_Triggers@&prex.lnk minus
select trigger_name from user_Triggers@&prex.lnk;
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 user_Triggers;
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 user_Triggers@&prex.lnk
where trigger_name=nam1;
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('. This table_name : '||table1);
dbms_output.put_line('. Remote 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('. This event: '||event1);
dbms_output.put_line('. Remote 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('. This type: '||type1);
dbms_output.put_line('. Remote: '||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('. This ref: '||ref1);
dbms_output.put_line('. Remote: '||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('. Local when_clause:');
dbms_output.put_line(when1);
dbms_output.put_line('. Remote 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('. Local status: '||status1);
dbms_output.put_line('. Remote 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('Local definition: ');
dbms_output.put_line(desc1);
dbms_output.put_line('Remote 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 REMOTE SCHEMA
PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select distinct name,type from user_source minus
select distinct name,type from user_source@&prex.lnk;
PROMPT
PROMPT
PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT MISSING PROCEDURES/PACKAGES/FUNCTIONS IN LOCAL SCHEMA
PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select distinct name,type from user_source@&prex.lnk minus
select distinct name,type from user_source;
create table &prex.comcod as
select distinct name,type from user_source intersect
select distinct name,type from user_source@&prex.lnk;
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 user_source a, &prex.comcod b
where a.name=b.name union
select a.name,a.type,a.line,a.text
from user_source@&prex.lnk a, &prex.comcod b
where a.name=b.name
minus
(select a.name,a.type,a.line,a.text
from user_source a, &prex.comcod b
where a.name=b.name
intersect
select a.name,a.type,a.line,a.text
from user_source@&prex.lnk a, &prex.comcod b
where a.name=b.name )) q;
PROMPT
PROMPT
PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT VIEWS MISSING IN THIS SCHEMA:
PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
create table &prex.common_views as
select view_name from user_views@&prex.lnk
intersect
select view_name from user_views;
select view_name from user_views@&prex.lnk
minus
select view_name from &prex.common_views;
PROMPT
PROMPT
PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT VIEWS MISSING IN REMOTE SCHEMA:
PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select view_name from user_views
minus
select view_name from user_views@&prex.lnk;
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(100000);
for c in c1 loop
select text,text_length into def1,len1
from user_Views where view_name=c.view_name;
select text,text_length into def2,len2
from user_Views@&prex.lnk where view_name=c.view_name;
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('Local text_length: ' || to_char(len1));
dbms_output.put_line('Remote 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(' local: ' || substr(def1,i,240));
dbms_output.put_line(' remote: ' || substr(def2,i,240));
end if;
i := i + 240;
end loop;
end if;
if length(def2) > length(def1) then
dbms_output.put_line('Remote longer than Local. Next 255 bytes: ');
dbms_output.put_line(substr(def2,length(def1),255));
end if;
end loop;
end;
/
drop database link &prex.lnk;
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.ind;
drop table &prex.ind1;
drop table &prex.ind2;
drop table &prex.comcons;
spool off
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


Privileges comparision

December 26, 2004 - 1:35 am UTC

Reviewer: Logan

Tom

I have 2 users named A and B.

I want to compare the privileges and roles granted to these 2 users, and display only the differences.

Can you show how I can do this?

Thanks

Tom Kyte

Followup  

December 26, 2004 - 12:37 pm UTC

Using the technique described in 
http://www.oracle.com/technology/oramag/oracle/05-jan/o15asktom.html
Comparing the Contents of Two Tables

this becomes pretty easy....




ops$tkyte@ORA9IR2> variable u1 varchar2(30)
ops$tkyte@ORA9IR2> variable u2 varchar2(30)
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec :u1 := 'OPS$TKYTE'; :u2 := 'SCOTT'
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select privilege, what,
  2         decode( count(src1), 1, :u1 ) CNT1,
  3         decode( count(src2), 1, :u2 ) CNT2
  4     from
  5   ( select 1 src1, to_number(null) src2, privilege, owner||'.'||table_name what from dba_tab_privs where grantee = :u1
  6     union all
  7     select 1 src1, to_number(null) src2, granted_role, 'ROLE' what from dba_role_privs where grantee = :u1
  8     union all
  9     select 1 src1, to_number(null) src2, privilege, 'SYSPRIV' what from dba_sys_privs where grantee = :u1
 10      union all
 11     select to_number(null) src1, 1 src2, privilege, owner||'.'||table_name what from dba_tab_privs where grantee = :u2
 12     union all
 13     select to_number(null) src1, 1 src2, granted_role, 'ROLE' what from dba_role_privs where grantee = :u2
 14     union all
 15     select to_number(null) src1, 1 src2, privilege, 'SYSPRIV' what from dba_sys_privs where grantee = :u2
 16    )
 17   group by privilege, what
 18  having count(src1) <> count(src2)
 19  /
 
PRIVILEGE               WHAT     CNT1      CNT2
----------------------- -------- --------- ---------
DBA                     ROLE     OPS$TKYTE
RESOURCE                ROLE               SCOTT
CREATE VIEW             SYSPRIV            SCOTT
SELECT ANY DICTIONARY   SYSPRIV  OPS$TKYTE
 
ops$tkyte@ORA9IR2> grant select on t to scott;
 
Grant succeeded.
 
ops$tkyte@ORA9IR2> @test
ops$tkyte@ORA9IR2> variable u1 varchar2(30)
ops$tkyte@ORA9IR2> variable u2 varchar2(30)
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec :u1 := 'OPS$TKYTE'; :u2 := 'SCOTT'
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select privilege, what,
  2         decode( count(src1), 1, :u1 ) CNT1,
  3         decode( count(src2), 1, :u2 ) CNT2
  4     from
  5   ( select 1 src1, to_number(null) src2, privilege, owner||'.'||table_name what from dba_tab_privs where grantee = :u1
  6     union all
  7     select 1 src1, to_number(null) src2, granted_role, 'ROLE' what from dba_role_privs where grantee = :u1
  8     union all
  9     select 1 src1, to_number(null) src2, privilege, 'SYSPRIV' what from dba_sys_privs where grantee = :u1
 10      union all
 11     select to_number(null) src1, 1 src2, privilege, owner||'.'||table_name what from dba_tab_privs where grantee = :u2
 12     union all
 13     select to_number(null) src1, 1 src2, granted_role, 'ROLE' what from dba_role_privs where grantee = :u2
 14     union all
 15     select to_number(null) src1, 1 src2, privilege, 'SYSPRIV' what from dba_sys_privs where grantee = :u2
 16    )
 17   group by privilege, what
 18  having count(src1) <> count(src2)
 19  /
 
PRIVILEGE               WHAT     CNT1      CNT2
----------------------- -------- --------- ---------
DBA                     ROLE     OPS$TKYTE
SELECT                  OPS$TKYT           SCOTT
                        E.T
 
RESOURCE                ROLE               SCOTT
CREATE VIEW             SYSPRIV            SCOTT
SELECT ANY DICTIONARY   SYSPRIV  OPS$TKYTE
 
 

default_length

September 01, 2005 - 12:33 pm UTC

Reviewer: A reader

what is the use of default_lenght column in user_tab_columns
and why it is different in every schema ?

I mean if I have schema1 and schema2 with exact same table structure , I can have default_legth differnt for
some column

info varchar2(100) default null

entry_date date default sysdate

if I observe default_length in shcema1 the corrosponding value are 5,8

if I observe default_length in schema2 the corrosponding value are 4,7

how come ?

TIA

Tom Kyte

Followup  

September 01, 2005 - 4:01 pm UTC

"show me"

scott-ORA10G> @connect /
scott-ORA10G> set termout off
ops$tkyte-ORA10G> @login
ops$tkyte-ORA10G> set termout off
ops$tkyte-ORA10G>
ops$tkyte-ORA10G> set termout on
ops$tkyte-ORA10G> drop table t;

Table dropped.

ops$tkyte-ORA10G> create table t( x varchar2(100) default null, y date default sysdate );

Table created.

ops$tkyte-ORA10G> select column_name, default_length from user_tab_columns where table_name = 'T';

COLUMN_NAME                    DEFAULT_LENGTH
------------------------------ --------------
X                                           4
Y                                           8

ops$tkyte-ORA10G> @connect scott/tiger
ops$tkyte-ORA10G> set termout off
scott-ORA10G> @login
scott-ORA10G> set termout off
scott-ORA10G>
scott-ORA10G> set termout on
scott-ORA10G> drop table t;

Table dropped.

scott-ORA10G> create table t( x varchar2(100) default null, y date default sysdate );

Table created.

scott-ORA10G> select column_name, default_length from user_tab_columns where table_name = 'T';

COLUMN_NAME                    DEFAULT_LENGTH
------------------------------ --------------
X                                           4
Y                                           8

 

comparing schema data

February 20, 2006 - 7:37 am UTC

Reviewer: A reader

I have two schemas in the same database with exactly
same table structures. I just want to know the
differences in the data of the tables.

Is there a script which can report DATA differences
between two schemas. I dont want to compare anything
else except the table data.

Thanks.


Tom Kyte

Followup  

February 20, 2006 - 8:09 am UTC

</code> http://www.oracle.com/technology/oramag/oracle/05-jan/o15asktom.html <code>

search for "comparing" on that page.

User management

August 22, 2006 - 9:27 am UTC

Reviewer: A reader

Are there any relevant topic that already discussed on Asktom about user management.

Tom Kyte

Followup  

August 27, 2006 - 2:22 pm UTC

what about user management?

Hidden tables

March 19, 2008 - 6:20 am UTC

Reviewer: Prasad Jayakumar from India

Hi Tom

Is there any possibility we get atleast one row running the following query?

select table_name from user_tab_columns
minus
select table_name from user_tables

I am not sure how, but I have seen hidden tables in Oracle supplied schema (ORABPEL). If there is a possibility, then compare schema script need to be tweaked. Am I right?

With Regards
Prasad Jayakumar
Tom Kyte

Followup  

March 24, 2008 - 9:40 am UTC

I don't know if your compare schema would need to be "tweaked" - so I cannot tell you if you are right or wrong.

(don't forget, recycle bin objects - they do not appear in user_tab_columns but they do in user_tables)


but why would getting at least "one row" be useful here? You could outer join to dual - to make up a row.... but not sure why or how that would be 'useful'

ops$tkyte%ORA10GR2> purge recyclebin;

Recyclebin purged.

ops$tkyte%ORA10GR2> with data as
  2  (
  3  select table_name from user_tab_columns
  4  minus
  5  select table_name from user_tables
  6  )
  7  select nvl(table_name,'nothing')
  8    from dual left outer join data on (1=1)
  9  /

NVL(TABLE_NAME,'NOTHING')
------------------------------
nothing

ops$tkyte%ORA10GR2> create table t (x int);

Table created.

ops$tkyte%ORA10GR2> drop table t;

Table dropped.

ops$tkyte%ORA10GR2> with data as
  2  (
  3  select table_name from user_tab_columns
  4  minus
  5  select table_name from user_tables
  6  )
  7  select nvl(table_name,'nothing')
  8    from dual left outer join data on (1=1)
  9  /

NVL(TABLE_NAME,'NOTHING')
------------------------------
BIN$STBNhUVsRNPgQAB/AQBeuQ==$0

More on SchemaDiff.sql?

April 21, 2008 - 1:16 pm UTC

Reviewer: Lloyd Lunham from Rochester, NY

Any chance of getting the full script of "SchemaDiff.sql"? Looks like it was truncated. From other research, it looks like it might have been derived from an Oracle Magazine "Code of the Week" article in March, 1999. I couldn't determine if that was the original source since Oracle Magazine's archives currently go back only as far as 2003.

found it

August 21, 2008 - 2:18 pm UTC

Reviewer: Jeff from Atlanta, GA

just found SchemaDiff.sql here:
http://www.angelfire.com/wi/jonasn/schemadiff.html

...where would we be without google. ;)

How to compare two oracle database schemas in Different oracle versons

January 01, 2009 - 2:59 am UTC

Reviewer: krishnakumar from India

Hi Tom,
i am new to oracle database, but i have knowledge in SQL query's. i have involved in migration testing in oracle data base. i am using TOAD tool as front end with TNS details. i don't have oracle software in my system. with the help of TNSnames.ora file i am connecting to oracle database. i have database A in oracle 9i and i have database B in oracle 10G. i have to compare the table structure in both database A and B i.e.columns name ,there data type and the length in each table. any new data types replaced for old data types. any data type signature or it will not support in 10g like that.
kindly help me.

Tools for Schema Comparison

May 22, 2009 - 2:54 pm UTC

Reviewer: Stefan Pflanz from Germany

If you don't want to use Oracle CMP (in fact a little bit tricky to understand) consider to use PowerDIFF for Oracle, see http://www.orbit-db.com This tool is comprehensible and compares two Oracle databases, database schemas or individual objects - both with respect to structure and/or table/view data. It's fast and reliable and also creates a diff script (to apply diff-and-merging).

A new schema comparison tool from Red Gate

January 17, 2010 - 5:56 am UTC

Reviewer: David Atkinson from Cambridge, UK.

At Red Gate we've been developing schema comparison and synchronization tools since 2001. It's only this year (2010) that we've released an Oracle compatible version. This can be downloaded from http://www.red-gate.com/Products/schema_compare_for_oracle/index.htm and is completely free to use during its early access phase. We'd welcome any feedback you may have.

David Atkinson, Product Manager, Red Gate Software

SQLEDT

February 08, 2011 - 12:55 pm UTC

Reviewer: Brian from NY, USA

Our tool, www.sqledt.com compares schemas across Oracle Databases, in addition to a host of other features.

April 15, 2011 - 10:24 am UTC

Reviewer: Amaresh from INDIA

Tom
Would like to know if there is compare of the value of two table & insert it another table.
there is not any common primary key in both the table.
Datatype & size of columns of both the table are same

As bellow the example

TABLE A
123 AB LONDAN
123 AB LONDAN
456 CD US
896 XY KANADA

TABLE B


123 LM LONDON
123 AB LONDAN
896 HK KANADA
793 JK INDIA



Its very urgent

Tom Kyte

Followup  

April 15, 2011 - 11:17 am UTC

if it is urgent, you would have told me HOW to compare these two things. If there is no key to join them, what are the grounds for comparison. How does it work.

Perhaps


Comparing the Contents of Two Tables
in
http://www.oracle.com/technetwork/issue-archive/2005/05-jan/o15asktom-084959.html

will help you?

Schema comparison -DIY disaster

April 15, 2011 - 3:43 pm UTC

Reviewer: john hawksworth from Guildford, Surrey, UK

I tremble and quake at the thought, but I disagree with Tom on this one.

His initial surmise that TOAD has a utility for schema comparison is correct, it does and it's superb. Note that I do not work for Quest (the makers of TOAD) I am just a humble user.

I disagree with Tom about any DIY approach to schema comparison, because an individual simply cannot do the job properly. TOAD's comparison tool has dozens and dozens of options, some of which I did not know existed, despite the fact that I have many years experience of Oracle. These are all easily configurable. My point is, that by purchasing such a tool, one acquires the distilled knowledge of many people, encapsulated in one utility. In a life time of working with Oracle, a sole programmer will never come across all the options imaginable.

This is just a particular case of why productivity tools do what they do. Any tool that gave access to such a wealth of knowledge, easily and at the click of a mouse is to be commended. For this reason, nobody in their right mind writes their own RDBMS, they buy one. Don't for one minute think that because each table and field is the same, it means that two schemas are the same.

This is vital when comparing production schemas, where apparently innocuous differences may have significant impact.

Red Gate's products are also excellent, but I have only used them on SQL Server, and hence can't comment on Oracle. However, if their quality in Oracle matches that of their SQL Server offerings, it ought to be considered.
Tom Kyte

Followup  

April 15, 2011 - 5:03 pm UTC

I would want to compare the table name, column name, data type and
also the size, if there is any difference, i would want the script to display
the db name, table name , field name and data type and length to be displayed.


given that requirement, it isn't really that hard.

And to update for the year 2011 - there is the change management pack, part of enterprise manager, that does this nowadays as well.


schema

April 15, 2011 - 7:27 pm UTC

Reviewer: A reader


Comparison Logic

April 18, 2011 - 8:11 am UTC

Reviewer: Dana from Phoenix, AZ USA

I am going to take a different viewpoint on this.
The premise I am challenging is that one or another schema is considered "standard". I would consider the scripts to be the standard; the valid should be comparing schema to script.
Many times, it is assumed that production is the standard, but often production deviates from the original DDL scripts. So unless the scripts are kept up to date and annotated, the validity of the comparison is suspect.

Difficult to maintain scripts

June 02, 2011 - 5:52 am UTC

Reviewer: Observer

I think when the application is in development phase, when too many changes and enhancements are going on, script maintenance can become not only difficult but can also slow down the development process. That is when we would require these comparison scripts and toad and all, to help keep the environments in sync.

And I don't despise Toad at all. Purists may think whatever they want to, but toad has many times helped me save lot of effort.
Tom Kyte

Followup  

June 02, 2011 - 9:08 am UTC

guess what your JOB IS. It is "software developer". It is not "let's hack together something we may or may not be able to figure out what changes we made or why - developer"


Slowing the development process - so as to maintain a clear picture of the decisions being made and why they are made - is a good thing, not a bad thing.

You know the old saying about haste.

Haste makes...

June 02, 2011 - 3:47 pm UTC

Reviewer: Dana from Phoenix, AZ USA

Haste makes tuning in production the normal procedure.
Where, usually, those who designed the schema, don't go because they don't do production support. They're "design" people.

Sriv -- Thanks for the question regarding "Schema Comparison", version 8.1.6

June 16, 2011 - 11:41 am UTC

Reviewer: Carlos Calderón from Costa Rica

Que tal Tom,

The next block of your code for remote schema comparision generated this error (Oracle RDBMS 9i):
ERROR at line 2:
ORA-01445: cannot select ROWID from a join view without a key-preserved table
ORA-02063: preceding line from AAALNK

PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT COMMON CONSTRAINTS, TABLE MISMATCH
PROMPT ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select a.constraint_name,a.table_name,b.table_name from
user_constraints a, user_constraints@&prex.lnk b,
(select z.constraint_name from
(select constraint_name, table_name from useR_constraints union
select constraint_name, table_name from user_constraints@&prex.lnk) 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;

I don´t know what happen. Can you correct the mistake?

Thanks.

June 20, 2012 - 8:10 am UTC

Reviewer: Shrikant from India

USE plsqldev to compare object under tool tab,,
and select second database and compare .