Compare tables in SQL Server and Oracle

Question

I have a set of SQL scripts to migrate data from tables in SQL Server 2005 to Oracle 11. I want to compare the data in two databases to ensure that they are migrated correctly.

My source is: SQL Server 2005. It has 10 big tables with approx 80 million rows and 20 tables with approx 100,000 rows.

My target is: Oracle 11 on Exadata, which has the similar table structure to its source.

Any idea what I can do? Are there any tools available?

Answer 1

You could maybe design a function that will for each column on each table:

• concatenate row number with value
• hash it (md5 should be available on both)
• and compute aggregate bitwise XOR of hash values.

If the results are the same you probably have the same values in your two databases without needing transferring large data over the network.

The drawback is: if the results are not the same you don't know where the difference lies. You could maybe partition your tables by chunks of 1000 rows or such for example.