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

Thread: Oracle Data compare Utility

  1. #1
    Join Date
    Jul 2001
    Posts
    2

    Question

    I'm looking for a tool which can compare datas ( data level ) between 2 oracle databases !

    Would appreciate very much if you could recommend me a tool

    Thanks in advance!

  2. #2
    Join Date
    Nov 2000
    Posts
    344
    Hi,

    If you want to make sure that data is the same between
    2 databases, the fastest way is probably to clone one
    database to another. Unless you have a small db, I
    think this is the fastest way.

    If you want something to compare table structures, etc,
    there are a number of tools available. One of them is
    at http://www.ezsql.net.

    If you want to compare the data just one table at a time,
    do it like this : (you don't need a tool!)

    select * from mytable
    MINUS
    select * from mytable@remote_db;

    If you don't get any rows, then reverse it like this :

    select * from mytable@remote_db
    MINUS
    select * from mytable;

    If you STILL don't get any rows, then your tables are identical.

    -John
    http://www.ezsql.net

  3. #3
    Join Date
    Jul 2001
    Posts
    2
    Hi,

    I have 100 tables to compare, column by column

    and I can't clone the database

    Best regards


  4. #4
    Join Date
    Feb 2001
    Posts
    75

    Data compare

    Hi,

    I am doing a similar work by procedural template and then writting a procedure for each table. My template does sequentail comparison and inserts column name & two values for columns having different values.

    I cound not find any thing better.

    In case you need details mail me at kailashkr@yahoo.com.

    Kailash Pareek

  5. #5
    Join Date
    Nov 2000
    Posts
    344
    Hi,

    OK, do this :

    select 'Select * from '|| owner || '.' || table_name || chr(13) || chr(10) ||
    'MINUS' || chr(13) || chr(10) ||
    'Select * from '|| owner || '.' || table_name || '@remote_db;' || chr(13) || chr(10) ||
    'Select * from '|| owner || '.' || table_name || '@remote_db' || chr(13) || chr(10) ||
    'MINUS' || chr(13) || chr(10) ||
    'Select * from '|| owner || '.' || table_name || ';'
    from dba_tables
    where owner = 'SYSTEM'

    (obviously, you'll have to substitute, or leave out the owner,
    and substitue the correct name for your db_link).

    Spool the output to a text file, then run it. It will produce rows
    for tables with identical structure but different rows.

    -John
    http://www.ezsql.net

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