Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Babu.

Asked: March 10, 2018 - 5:26 am UTC

Last updated: March 11, 2018 - 3:24 am UTC

Version: Oracle12c

Viewed 1000+ times

You Asked

Is there any tools available to compare data (data validation post Sybase migration) between Sybase and Oracle 12c which high volume up to 1tB.

SQL developer takes a long time even for a small set of data

and Connor said...

I think SQL Developer is predominantly about migrating the metadata, and sample data across, so that you have get an Oracle schema up and running quickly to do testing etc.

When it comes to unloading/loading large quantities of data (and comparing it) I would say you are best using tools native to the particular product.

For example, for volumes up in the 1TB range, I'd be doing some home-grown comparisons, for example, I could apply an MD5 to each column, and sum up the values, and then compare to the same query in Sybase. I've split the MD5 into 2 halves to keep the number sizes under control

with hashvals as 
  ( select 
      standard_hash(col1,'MD5') c1,
      standard_hash(col2,'MD5') c2,
      standard_hash(col3,'MD5') c3,
     ...
   from migrated_table
 )
select 
  sum(to_number(substr(c1,1,16),'xxxxxxxxxxxxxxxx')),
  sum(to_number(substr(c1,17,16),'xxxxxxxxxxxxxxxx')),
  sum(to_number(substr(c2,1,16),'xxxxxxxxxxxxxxxx')),
  sum(to_number(substr(c2,17,16),'xxxxxxxxxxxxxxxx')),
  sum(to_number(substr(c3,1,16),'xxxxxxxxxxxxxxxx')),
  sum(to_number(substr(c3,17,16),'xxxxxxxxxxxxxxxx')),
...
...
from hashvals


and then do the same against my original sysbase data.

This gives the whole power of SQL to you - I can do it across the whole table like above, or do GROUP BY to break it up into logical units etc

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library