Compare two identical tables data in different Oracle Databases
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Compare two identical tables data in different Oracle Databases

Hybrid View

  1. #1
    Join Date
    Jun 2003
    Posts
    2

    Compare two identical tables data in different Oracle Databases

    Hi Oracle Gurus,
    PLease I need immediate attention of someone for this problem. We are testing our application to see whether on 8i DB and 9i DB, the data has posted correctly in all columns of set of tables.

    We need to compare every column data in two identical structure tables in too different versions of Oracle Databases.
    One crude method I found is to do
    "select * from 8i_table
    minus
    select * from 9i_table"

    But as we have more than 60-70 columns and thousands of rows in the table, it is becoming costly and very slow.

    What would be the best way to do this? We are in the process of migrating to 9i before that user wanted this test to be done.

    Thanks and appreciate your help in advance.
    I am just waiting for your expert comments and feedback.
    If you know any link to the script developed by you or someone is also fine.

    Thanks Again
    sat Bob

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    The set operations INTERSECT and MINUS are all you need for that. I don't know why you say that they are crude -- they are concise, and do exactly what you want.

    Do you want instead to create some kind of full outer-join between the two tables, and look for differences (including correct null-handling) between the tables? Forget it.

    select count(*) from tablea;

    select count(*) from tableb;

    if you find a difference there, then you already know there is a problem.

    Also try...

    select count(*) from
    (select * from tablea
    intersect
    select * from tableb)

    .. to see how many rows are exactly the same.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    If you're comparing two db's then one of them must be remote? Yes? then the network is likely to be the rate-limiting factor.

    - If you have them on the same machine, try IPC as the protocol - never timed it, but logic says it should be faster.

    - If not, have a word with the network chappie - perhaps you're only using 10Mz? (you shouldn't have probs with "thousands" of rows - I've do this kind of thing for 100'000 rows, avg.row length 500 - took something like 20 minutes).

    As slimdave says, avoid a join like the plague.

  4. #4
    Join Date
    Feb 2003
    Location
    London
    Posts
    170
    I'm having the same problem here.
    I've got two identical tables of which one is a remote db. They both have over 6 million records, but on doing count(*) on these table, there is a difference of 140 rows. So, I'm trying to find the rows using select col1,col2,col4,col5 from db1
    minus
    select col1,col2,col4,col5 from db2@remotesite

    but this is taking ages. I started the run at 9.19 and it's 10.02 now and havent got any results yet. Is there any way I can speed this up. I'm using oracle 9.0.1.3.0. Any help would be very much appreciated.

    Thanks a lot

  5. #5
    Join Date
    Feb 2004
    Posts
    77
    It is an option. Using UNIX as OS

    Export both tables into a comma delimited files
    Sort both the files.
    Use diff to find the differences.

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