Tool to find "DATA" Difference in Oracle tables

[naresh]

Hi,
I have a Question can I find a tool which gives the "DATA" Difference
between 2 tables of 2 Different Schemas in the same or Different Databases.
say for eg.
We have an EMP table in Schema-A with 5 Records
and another EMP table in Schema-B with 8 Records
I need a tool which is able to get the "DATA" Difference
in each Column b/w these 2 tables and it should also
list the 3 non existing records in Schema-A. a
report should be generated which Displays Difference in the Column
values and the Non existing records too.
I found some tools which gives the Structure ,
Constraint,Index etc... Difference but not the "DATA" Difference

help..
Thanx in advance
Naresh

 

[MikeJones]

Try TOAD (

http://www.toadsoft.com)

or if your sql is handy enough you could create a DB link between the two DB's and work it out yourself.

 

[naresh]

Hi Mike ,
Thanks
but its is the same which gives the structural difference but not the data difference contained in the tables I wan't the data in the tables to be compared. and get the difference in data. is there something ?

Thanx ,
Naresh Naresh

 

[karluk]

I can't vouch for the product because we don't have it, but DBDiff for Oracle looks like a possibility. Check out the following website for details.

http://www.dkgadvancedsolutions.com/oradbdiff.htm

 

[MikeLacey]

naresh, did you get something that does what you need?
Mike
michael.j.lacey@ntlworld.com

 

[naresh]

No not yet Mike !!!!

I downloaded the Product DBDiff Karluk was saying about it gives vague report
and does not give the data diff report.
help please...

thanx in advance
Naresh Naresh

 

[MikeLacey]

Naresh,

How large is the database? Would it be possible to export each table to a text file (using sqlplus) and then use the unix diff command to compare the data?
Mike
michael.j.lacey@ntlworld.com

 

[naresh]

Hi Mike and Karluk,

I think the DB Diff Tool what Karluk said works.
I tried with Microsoft ODBC for Oracle(Data source) it
works and gives the entire row of the table in whichever
column it finds a difference.
But the DBDiff tool was not working Properly with
Oracle ODBC Driver.
Ok anyway that's good something worked
and Our Database is of medium sized and has around
1200 tables and some tables are very big.

Many Thanks to U Guys
It really helped

Naresh
(naresh_deol@yahoo.com) Naresh

 

[MikeLacey]

Great, and thanks for letting us know that DbDiff worked for you, I'll bear it in mind.
Mike
michael.j.lacey@ntlworld.com

 

[deepak007]

Hi Mike
I have tried toead to find out the difference between the two schemas ( two diff database ) say db1 and db2. But the problem which i am facing is if the two database ( db1 and db2) are on the same servers then there is no problem but if the two database are on the fifferent servers then it will give an error TNS: could not resolve service name..
Help me....

 

[carp]

The error message is telling you that your tool can't find a definition for the TNS alias you are supplying. Check your TNSNAMES.ORA to make sure you are providing the correct service name.

As for finding data differences, you can use simple SQL:

SELECT * FROM table1
MINUS
SELECT * FROM table2;

will give you the rows that are in table1 that aren't in table2.

If the tables are in different databases, then you will need to use dblinks and change the query to:

SELECT * FROM table1
MINUS
SELECT * FROM table2@remote_instance_alias;

 

[raygg]

A slight corretion:

This does not work:

SELECT * FROM table1
MINUS
SELECT * FROM table2;

However, this DOES work:

SELECT rownum from table1
MINUS
SELECT ROWNUM FROM TABLE2;

 

[MikeJones]

raygg,

This will only give you the row numbers from 1 to the total number of rows with no thought to the actual contents of each rows data, so if both tables have 10 rows you query will say they are the same, even if the actual data is co,mpletely different.

as an example try

create table mike_a (text varchar2(1))
/
create table mike_b (text varchar2 (1))
/
insert into mike_a values ('A')
/
insert into mike_a values ('B')
/
insert into mike_a values ('C')
/
insert into mike_a values ('D')
/
insert into mike_a values ('E')
/
insert into mike_b values ('1')
/
insert into mike_b values ('2')
/
insert into mike_b values ('3')
/
insert into mike_b values ('4')
/
insert into mike_b values ('5')
/
SELECT rownum from mike_a
MINUS
SELECT ROWNUM FROM mike_b
/
no rows selected


But both tables contain totally different data!!