data comp

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.

Thanks
basiranaAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
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);
commit;


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
begin
	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;
	commit;
end;
/

--generate tab2 checksums
begin
	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;
	commit;
end;
/

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

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
basiranaAuthor Commented:
How do we do that. Any sample script?
0
 
Steve WalesSenior Database AdministratorCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.