data comp

Posted on 2012-09-07
Medium Priority
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
  • 2
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38377236
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

ID: 38377428
How do we do that. Any sample script?
LVL 78

Accepted Solution

slightwv (䄆 Netminder) earned 2000 total points
ID: 38377705
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(i.id, 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(i.id, owa_opt_lock.checksum('BUD','TAB2', i.rowid));
	end loop;

select t1.id from tab1_checksums t1, tab2_checksums t2
where t1.id=t2.id and t1.checksum != t2.checksum

Open in new window

LVL 23

Expert Comment

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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
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…
Suggested Courses

864 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