Differences

Here’s an offering in my “drafts for someone else to finish” suite of postings; it’s one I’ve been meaning to retest and publish for quite some time. It’s a possible answer to a question that comes up occasionally on the OTN database forum: “How do I resynchronize two tables that are supposed to be copies of each other?”

I suppose it’s possible to interpret this question in a couple of ways, but sometimes it means – “anything in table 1 should also be in table 2, anything in table 2 should also be in table 1, and where a row exists it should be exactly the same in both tables”. There are two “philosophical” problems attached to the task, of course – first, how do you decide that two rows that are currently different from each other are supposed to be the same; second how do you decide which bits of which version hold the correct values.

Whatever the detail of your final decision it’s likely to be a messy and, perhaps more importantly, slow job to do. If your requirement is very simple, though, you may not need to write much code to achieve it if you take a look at the package dbms_rectifier_diff (see script dbmsrctf.sql in $ORACLE_HOME/rdbms/admin). The package has been around since at least Oracle 8i though, according to my notes, I didn’t play around with it until March 2002 and 9.0.1.2; it contains procedures to find differences and to rectify differences; here’s a simple demonstration of the use (or possibly abuse) of the package that I haven’t tested in more than 10 years:

rem
rem	Create the tables to be compared. They MUST have primary keys declared
rem

drop table t1;
drop table t2;

create table t1
nologging
as
select
	rownum			id,
	to_char(rownum)		small_vc,
	rpad(rownum,100,'x')	padding
from
	all_objects
where
	rownum <= 1000
;

alter table t1 add constraint t1_pk primary key (id) nologging;

delete from t1
where id in (22, 300, 850);

create table t2
nologging
as
select
	rownum			id,
	to_char(rownum)		small_vc,
	rpad(rownum,100,'x')	padding
from
	all_objects
where
	rownum <= 1000 
; 

alter table t2 add constraint t2_pk primary key (id) nologging;
 
delete from t2 where id in (55, 475, 760);
 
commit;
 
-- you could gather some stats at this point
 
rem 
rem	Create the report tables 
rem	One table matches the structure of the data being compared 
rem	(which need not be ALL columns in the base tables) 
rem	The other table will be used to list rowids from the first table,  
rem	and tell you which base table it is missing from. 
rem 

drop table diff_data; 
drop table diff_rowids; 

create table diff_data (
 	id		number, 
	small_vc	varchar2(40), 
	padding		varchar2(100) 
); 

create table diff_rowids(
 	r_id		rowid,
 	present		varchar2(4000),
 	absent		varchar2(4000) 
) 
; 

rem 
rem	First call the DIFFERENCES procedure 
rem 

begin
     dbms_rectifier_diff.differences(
 	sname1 			=> user,	-- reference schema
	oname1			=> 'T1',	-- reference table
	reference_site 		=> null,	-- reference site (null => current) db_link
	sname2			=> user,	-- target schema
	oname2			=> 'T2',	-- target table
	comparison_site		=> 'jplcopy',	-- target site (null => current) db_link
	where_clause		=> 'id <= 1000',
 						-- optional where clause to restrict rows
 	column_list		=> null,	-- e.g. 'id,padding' , null=> all,
						-- leave no spaces around commas
	missing_rows_sname	=> user,	-- output schema,
	missing_rows_oname1	=> 'DIFF_DATA',	-- table to list mismatching data
	missing_rows_oname2	=> 'DIFF_ROWIDS',-- table to locate mismatching data
	missing_rows_site	=> null,	-- output site (null => current) db_link
	max_missing		=> 10,		-- how many differences before stopping
	commit_rows		=> 100		-- how many rows to commit in output
	);
end;
/

column	absent	format a30
column	present	format a30

select * from diff_rowids;

column id	format 999
column small_vc	format a10
column padding	format a10

select
	rowid, id, small_vc, substr(padding,1,10) padding
from
	diff_data
;

rem
rem	Now call the RECTIFY procedure
rem	This reads the existing DIFF tables, and distributes the data accordingly.
rem

begin
    dbms_rectifier_diff.RECTIFY(
	sname1 			=> user,	-- reference schema
	oname1			=> 'T1',	-- reference table
	reference_site 		=> null,	-- reference site (null => current)
	sname2			=> user,	-- target schema
	oname2			=> 'T2',	-- target table
	comparison_site		=> 'jplcopy',	-- target site (null => current)
	column_list		=> null,	-- e.g. 'id,padding' , null=> all,
						-- leave no spaces around commas
	missing_rows_sname	=> user,	-- output schema,
	missing_rows_oname1	=> 'DIFF_DATA',	-- table to list mismatching data
	missing_rows_oname2	=> 'DIFF_ROWIDS',-- table to locate mismatching data
	missing_rows_site	=> null,	-- output site (null => current)
	commit_rows		=> 100		-- how many rows to commit in output
	);
end;
/

According to the header notes in the dbmstrctf.sql script, nothing changed between 1991 and 2010 (11.1), and then a couple more comments appear in the 12c timeline. I won’t make any guarantees about how the package behaves (the code, when traced in 9.0, was dire) but perhaps this script will be a pointer to a good enough solution for people facing the need to resynchronise data sets occasionally.

Footnote:

It was a discussion of this type of problem at OpenWorld 2013 – and a comment about a licensed option using the package dbms_comparison – that prompted me to resurrect this very old script.