dcsimg
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: compare two different schemas

  1. #1
    Join Date
    Jan 2001
    Posts
    2,828

    Talking

    Hi All

    I have a two databases db1 and db2 .... what i need to do is the following.

    select * from table1 in db1
    minus
    select * from table1 in db2

    and produce a report saying that

    a) table1 in db1 has the same records as in table1 in db2 if the above the query does not return any rows.

    else


    produce a report saying that table1 in db1 has different records then table1 in db2...

    there are many tables like this in db1 and db2 like table1 ,table 2 etc which need to be compared.Any ideas on how to write a PL/sql program for this.

    regards
    Hrishy


  2. #2
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    Hi Hrishy,

    Check this script, maybe it will usefull for you:

    http://www.dbascripts.com/scripts/in...criptNumber=48

    Cheers

    Angel

  3. #3
    Join Date
    Jan 2001
    Posts
    2,828

    Talking

    Hi Angel

    The script you mentioned compares schemas .I need to compare the data within the tables.

    regards
    Hrishy

  4. #4
    Join Date
    Mar 2002
    Posts
    301
    Hi Hrishy,

    Check out metalink.oracle.com for any readymade scripts.
    otherwise, create a script by yourself.

    I am under the assumption that both the databases has similar records,
    in it.

    Write a stored procedure, with 2 cursors.
    One cursor selecting all the table_names from the schema and the Second cursor
    from the second database. Within these 2 cursors, you should create a REF cursor which
    will construct a query from both these cursors. If the ref cursor gives you any records, then
    you should store it in some table.

    Vijay.

  5. #5
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422

    db_link?

    What about db_links?
    You can create database link on db1 to db2.
    Then you could access tableX@db2_link while beeing connected to db1.

    You could query like this
    select * from table1 --local
    minus
    select * from table2@db2_link

    Regards,
    Tomaz

  6. #6
    Join Date
    Oct 2000
    Location
    Charlotte, USA
    Posts
    330
    Yes,
    Tomazz concept is Good.Db_link will work.
    That is the best way to compare all objects in both databases.
    Thanigaivasan

  7. #7
    Join Date
    Jan 2001
    Posts
    2,828

    Talking

    Hi All

    Yep i am working on the concept of dblinks and using sql to construct sql.Thanknx for your efforts and advice

    regards
    Hrishy

Posting Permissions

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


Click Here to Expand Forum to Full Width