data comp

Posted on 2012-09-07
Last Modified: 2013-08-07
We are replicating data between oracle databases. We want to run auditing to compare data differences. The database are having different character set. We are looking for faster method of comparing source and target databases. Do you have any scripts. Usually any operation to compare data over DB Links will be very expensive task We need faster and less expensive way of comparing data.

Question by:basirana
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    Can you hash/checksum the data in the columns locally then compare the hash's across the link?

    Still uses a link but the amount of data should be a lot less.

    Author Comment

    How do we do that. Any sample script?
    LVL 76

    Accepted Solution

    Here is a simple test case.

    Of course tab2 would be in the other database and the final check would use the link but it shows the concept.

    drop table tab1 purge;
    create table tab1(id number, col1 varchar2(10), col2 date, col3 number);
    drop table tab2 purge;
    create table tab2(id number, col1 varchar2(10), col2 date, col3 number);
    insert into tab1 values(1,'Hello',to_date('01/01/2001','MM/DD/YYYY'),1);
    insert into tab1 values(2,'World',to_date('02/02/2002','MM/DD/YYYY'),2);
    insert into tab2 values(1,'Hello',to_date('01/01/2001','MM/DD/YYYY'),1);
    insert into tab2 values(2,'World',to_date('02/03/2002','MM/DD/YYYY'),2);
    drop table tab1_checksums purge;
    create table tab1_checksums(id number, checksum number);
    drop table tab2_checksums purge;
    create table tab2_checksums(id number, checksum number);
    --generate tab1 checksums
    	for i in (select id, rowid from tab1) loop
    		insert into tab1_checksums values(, owa_opt_lock.checksum('BUD','TAB1', i.rowid));
    	end loop;
    --generate tab2 checksums
    	for i in (select id, rowid from tab2) loop
    		insert into tab2_checksums values(, owa_opt_lock.checksum('BUD','TAB2', i.rowid));
    	end loop;
    select from tab1_checksums t1, tab2_checksums t2
    where and t1.checksum != t2.checksum

    Open in new window

    LVL 22

    Expert Comment

    by:Steve Wales
    This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
    This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now