Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    Chennai
    Posts
    53

    Unanswered: comparing schemas

    how can we compare 2 schemas in an oracle database
    -Mathan
    For a quick pocket reference of oracle refer http://pocketoracle.blogspot.com/

  2. #2
    Join Date
    Nov 2002
    Posts
    833
    for the objects itself it is best to do it with some tool ; I for myself do it on necessity with powerdesigner using their reverse engineering capability

    if you want to extend it to all the stored values in tables ... then generic scripts are my choice

  3. #3
    Join Date
    Jan 2004
    Posts
    9

    you can use quests TOAD software

    it has the "Compare Schemas" function.

    other way is to generate two schema script (TOAD, SQL-Navigator, SQLPLUS...) and compare them with a good editor (Ultraedit...)

  4. #4
    Join Date
    Feb 2004
    Location
    Chennai
    Posts
    53

    comparing schemas

    Quote Originally Posted by osy45
    if you want to extend it to all the stored values in tables ... then generic scripts are my choice
    could u explain what u mean by generic scripts....
    -Mathan
    For a quick pocket reference of oracle refer http://pocketoracle.blogspot.com/

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Here's a naff script I wrote to compare SCHEMA2 to a BASELINE_SCHEMA. Replace these with your schema names. Just does a simple table comparison, nothing fancy like indexes etc though it would be easy to add.

    Alan

    select 1 idx, 'Table missing '||table_name
    from
    ( select table_name from dba_tables where owner='BASE_SCHEMA'
    minus
    select table_name from dba_tables where owner='SCHEMA2'
    )
    union
    select 2, 'Column missing ' || table_name||'.'||column_name
    from
    (
    select table_name, column_name from dba_tab_columns
    where owner='BASE_SCHEMA' and table_name in (select table_name from dba_tables where owner='BASE_SCHEMA')
    minus
    select table_name, column_name from dba_tab_columns where owner='SCHEMA2'
    ) where table_name not in
    ( select table_name from dba_tables where owner='BASE_SCHEMA' minus select table_name from dba_tables where owner='SCHEMA2')
    union
    select 3, 'Column to be removed ' || table_name||'.'||column_name
    from
    (
    select table_name, column_name from dba_tab_columns
    where owner='SCHEMA2' and table_name in (select table_name from dba_tables where owner='BASE_SCHEMA')
    minus
    select table_name, column_name from dba_tab_columns where owner='BASE_SCHEMA'
    ) where table_name not in
    ( select table_name from dba_tables where owner='BASE_SCHEMA' minus select table_name from dba_tables where owner='SCHEMA2' )
    union
    select 4, 'Column of the wrong datatype ' || table_name||'.'||column_name ||' should be of type ' ||data_type
    from
    (
    select table_name, column_name, data_type from dba_tab_columns where owner='BASE_SCHEMA'
    and table_name in (select table_name from dba_tables where owner='BASE_SCHEMA')
    minus
    select table_name, column_name, data_type from dba_tab_columns where owner='SCHEMA2'
    )
    where table_name not in
    ( select table_name from dba_tables where owner='BASE_SCHEMA' minus select table_name from dba_tables where owner='SCHEMA2' )
    order by 1

  6. #6
    Join Date
    Nov 2002
    Posts
    833
    for every table in question:

    set colsep ','
    spool csv.txt
    select *

    from table T1
    spool off
    or even better
    create a link to one of your schemas or grant the approriate rights

    select * from schema1.T1@<link>
    minus
    select * from schema2.T1
    & you will get the differences in values

    or
    select 'select * from '|| owner || '.' || table_name || ' minus ' || 'select * from || 'schema2' || '.' || table_name from all_tables where owner in (<owner1, owner2>);

    then execute to generated & spooled sql


    p.s. you can make use of dbms_matadata package for the structures

    see: http://www.dba-village.com/dba/villa...ils?TipId=1393

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •