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

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.

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.

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

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.