CODEWITHSUNDEEP
oracle-databasesql-server-2005comparison
Vikram
Vikram

Reputation: 604

Compare tables in SQL Server and Oracle

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?

Upvotes: 3

Views: 6385

Answers (3)

bernd_k
bernd_k

Reputation: 11966

I would try to set up a linked server in SSMO to the Oracle database. Then I would compare the tables using the except operator for each table

select * from table1
except
select * from [link_server]..myschema.TABLE1
union
select * from [link_server]..myschema.TABLE1
except
select * from table1

NOTE: I didn't get it to work, when the oracle tables contain CLOB columns.

Answer to comment: Install sql server on your local machine, there you are DBA. When you have access to remote machine, you can add a linked server to it.

Upvotes: 0

Benoit
Benoit

Reputation: 79243

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.

Upvotes: 1

duffymo
duffymo

Reputation: 309028

What you seem to be asking is: "How can I unit test my ETL solution to ensure that the data I have in my source database is faithfully replicated in my target database?"

When you go into the details of number of tables and their sizes, it suggests to me that you want to go to the depths of comparing every value in every column in every table.

I'm not aware of any such tools. Such a check could run for a long time, but since it's a one shot it's doable. You could write such a thing in C# or Java or another language you're comfortable in.

Another thought would be to check a smaller subset of each table. If the ETL tools does a small subset properly, there's no reason to believe that it'll fail with the rest unless there's an exceptional situation.

You could migrate the data in batches, checking and committing each one as you go.

Another idea might be a statisical approach: take a random sampling of rows from each and perform the checks.

Upvotes: 0

Related Questions