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

Thread: tools or script to show data & structure difference between two databases

  1. #1
    Join Date
    May 2002
    Location
    USA
    Posts
    462

    tools or script to show data & structure difference between two databases

    Hi friends,

    i would like to know if there is any tool to find and generate a script to show data difference in 2 schemas in different databases .

    I saw toad 8.5 version it gives only structural difference .

    i was looking if anyone has come across any such tool which generates data difference script too.

    we might manually prepare that . but number of records and referential constraints are so huge making us difficult to manually prepare
    siva prakash
    DBA

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    there is no such tool, it's very easy to make one anyways (very slow of course)

  3. #3
    Join Date
    Jan 2001
    Posts
    642
    Correct - I don't think there is any 'easy' tool to do both.

    For the structures, you may look at OEM -> Change manager
    and for data, good old sql :-)
    There is always a better way to do the things.

  4. #4
    Join Date
    Mar 2000
    Location
    Atlanta, GA,USA
    Posts
    155
    If all you need is to compare two schemas entirely, you can use DBMS_RECTIFIER_DIFF package against data dictionary objects on the schema level such as USER_TABLES, USER_INDEXES, USER_TAB_COLUMNS, etc...

    If it does not work againt data dictionary views (that is possible), you can put together a simple script to take a snapshot from them into a set of tables and compare them.

    Good luck,
    Sergey Popov
    Sr. Oracle DBA
    Bank of America

  5. #5
    Join Date
    Mar 2000
    Location
    Atlanta, GA,USA
    Posts
    155
    I've just took a shower and got a minor correction in the process. You should take a snapshot from the data dictionary views no matter what otherwise you will get an incorrect result because of statistics difference in the views. In additional to that these views are created on several base tables and will work slower on comparrison itself. So, take snapshots, get only columns you need and go ahead with DBMS_RECTIFIER_DIFF.

    PS: I need to move my desk to the shower. I get most of my ideas in there

    Sergey Popov
    Sr. Oracle DBA
    Bank of America

  6. #6
    Join Date
    May 2003
    Location
    Pretoria, Rep of South Africa
    Posts
    191

    Cool

    I would use the MINUS operator.Write a procedure to get pk||' '||non-unique value of each row in each user_table. loop the return and put your result in a temp_table.



    ouput should look like this:

    select ic2.id||' '||ic2.table_name
    from class@other_db ic2
    minus
    select ic.id||' '||ic.table_name
    from class ic

    Able was I ere I saw Elba

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