Try this script, you will need to change user names etc, but it should
work..
col table_name format a30
col column_name format a30
col medph2_format format a30
col med_test_format format a30
set feed off
set lines 150
set pages 66
spool match_schema.lst
--
-- MATCHING THE TABLES -----------------------------------------
--
ttitle 'THE FOLLOWING TABLES EXIST IN USERA AND DOESN'T EXIST IN USERB'
select table_name from all_tables a
where owner = 'USERA'
and not exists (select 1 from all_tables b
where owner = 'USERB'
and b.table_name = a.table_name)
order by table_name
/
ttitle 'THE FOLLOWING TABLES EXIST IN USERB AND DOESN'T EXIST IN USERA'
select table_name from all_tables a
where owner = 'USERB'
and not exists (select 1 from all_tables b
where owner = 'USERA'
and b.table_name = a.table_name)
order by table_name
/
--
-- MATCHING THE COLUMNS -----------------------------------------
--
ttitle 'THE FOLLOWING COLUMNS EXIST IN USERA AND DOESN'T EXIST IN USERB'
select table_name, column_name from all_tab_columns a
where owner = 'USERA'
and not exists (select 1 from all_tab_columns b
where owner = 'USERB'
and b.table_name = a.table_name
and b.column_name = a.column_name)
order by table_name, column_id
/
ttitle 'THE FOLLOWING COLUMNS EXIST IN USERB AND DOESN'T EXIST IN USERA'
select table_name, column_name from all_tab_columns a
where owner = 'USERB
and not exists (select 1 from all_tab_columns b
where owner = 'USERA'
and b.table_name = a.table_name
and b.column_name = a.column_name)
order by table_name, column_id
/
ttitle 'THE FOLLOWING COLUMNS HAVE DIFFERENT CHARACTERISTICS IN EVERY
SCHEMA'
select a.table_name, a.column_name,
decode(a.data_type,'DATE','DATE',
'NUMBER',decode(a.data_scale,0,'NUMBER('||a.data_precision||')',
'NUMBER('||a.data_precision||','||a.data_scale||')'
),
a.data_type||'('||a.data_length||')'
)||' '||decode(a.nullable,'N','not null',' ') USERA_format,
decode(b.data_type,'DATE','DATE',
'NUMBER',decode(b.data_scale,0,'NUMBER('||b.data_precision||')',
'NUMBER('||b.data_precision||','||b.data_scale||')'
),
b.data_type||'('||b.data_length||')'
)||' '||decode(b.nullable,'N','not null',' ') USERB_format
from all_tab_columns a,
all_tab_columns b
where a.owner = 'USERA'
and b.owner = 'USERB'
and a.table_name = b.table_name
and a.column_name = b.column_name
and (a.data_type <> b.data_type
or a.data_length <> b.data_length
or a.data_precision <> b.data_precision
or a.data_scale <> b.data_scale
or a.nullable <> b.nullable)
order by 1, 2
/
--
-- MATCHING THE VIEWS -----------------------------------------
--
ttitle 'THE FOLLOWING VIEWS EXIST IN USERA AND DOESN'T EXIST IN USERB'
select view_name from all_views a
where owner = 'USERA'
and not exists (select 1 from all_views b
where owner = 'USERB'
and b.view_name = a.view_name)
order by view_name
/
ttitle 'THE FOLLOWING VIEWS EXIST IN USERB AND DOESN'T EXIST IN USERA'
select view_name from all_views a
where owner = 'USERB
and not exists (select 1 from all_views b
where owner = 'USERB'
and b.view_name = a.view_name)
order by view_name
/
--
-- MATCHING THE CONSTRAINTS -----------------------------------------
--
ttitle 'THE FOLLOWING CONSTRAINTS EXIST IN USERA AND DOESN'T EXIST IN USERB'
select table_name, constraint_name, constraint_type
from all_constraints a
where owner = 'USERA'
and constraint_type <> 'C'
and not exists (select 1 from all_constraints b
where owner = 'USERB'
and b.table_name = a.table_name
and b.constraint_name = a.constraint_name
and b.constraint_type = a.constraint_type)
order by table_name, constraint_name
/
ttitle 'THE FOLLOWING CONSTRAINTS EXIST IN USERB AND DOESN'T EXIST IN USERA'
select table_name, constraint_name, constraint_type
from all_constraints a
where owner = 'USERB'
and constraint_type <> 'C'
and not exists (select 1 from all_constraints b
where owner = 'USERA'
and b.table_name = a.table_name
and b.constraint_name = a.constraint_name
and b.constraint_type = a.constraint_type)
order by table_name, constraint_name
/
--
-- MATCHING THE INDEXES -----------------------------------------
--
ttitle 'THE FOLLOWING INDEXES EXIST IN USERA AND DOESN'T EXIST IN USERB'
select table_name, index_name
from all_indexes a
where owner = 'USERA'
and not exists (select 1 from all_indexes b
where owner = 'USERB'
and b.table_name = a.table_name
and b.index_name = a.index_name)
order by table_name, index_name
/
ttitle 'THE FOLLOWING INDEXES EXIST IN USERB AND DOESN'T EXIST IN USERA'
select table_name, index_name
from all_indexes a
where owner = 'USERB'
and not exists (select 1 from all_indexes b
where owner = 'USERA'
and b.table_name = a.table_name
and b.index_name = a.index_name)
order by table_name, index_name
/
spool off
edit match_med.lst
This will list the schema differences, you then have a little work to
synchronise the two..
HTH
Mark
"What's up Doc?" Bugs Bunny
-----Original Message-----
Sent: Friday, February 09, 2001 03:35
To: Multiple recipients of list ORACLE-L
hi all gurus...
I have 2 user, eg : A and B.
They have their own object or shema, and some object are same.
Now, I have to synchronize their objects, because I want A and B have same
objects or schema.
If they have some object which last ddl time will be use.
Any suggestion ?
Thank u
Loli
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mark Leith
INET: mark_at_cool-tools.co.uk
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 Feb 09 2001 - 05:20:45 CST
