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: sync objects between user's

RE: sync objects between user's

From: Mark Leith <mark_at_cool-tools.co.uk>
Date: Fri, 09 Feb 2001 03:20:45 -0800
Message-ID: <F001.002B0166.20010209015556@fatcity.com>

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

Original text of this message

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