Oracle Scratchpad

December 9, 2013

Differences

Filed under: Oracle — Jonathan Lewis @ 5:48 pm BST Dec 9,2013

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.

9 Comments »

  1. Jonathan, what exactly do you want done? Are we supposed to just test and put results into comments?

    I intend to start blogging in the next few weeks and table synchronization will be one of my topics. If no one else beats me to it, I would prefer to deal with this method in a blog post with more formatting control and more space. I would link back here of course.

    What is your preference?

    Comment by Stew ASHTON — December 9, 2013 @ 8:26 pm BST Dec 9,2013 | Reply

    • Stew,

      Whatever you prefer.

      Using your own blog probably makes it easier to format your work, and adding a comment here that points to your blog would probably be a good way to make the connection.

      Comment by Jonathan Lewis — December 9, 2013 @ 8:35 pm BST Dec 9,2013 | Reply

  2. Hi, Jonathan.

    I plan to use the “DBMS_COMPARISON” to verify the difference between the two tables.
    “DBMS_RECTIFIER_DIFF” from “DBMS_COMPARISON” is better What?

    Kengo.

    Comment by いのうえけんご (@kengo1255) — December 10, 2013 @ 1:33 am BST Dec 10,2013 | Reply

    • Kengo,

      As it says in the footnote the indication I had about dbms_comparison (I got the name wrong originally) was that it was part of a licenced option – which is why it might be worth looking at dbms_rectifier_diff. If I’m wrong about the licensing then you could try a simple test of the code I’ve given and the equivalent for dbms_comparison to see if there are any significant differences in performance or implementation that you might care about. (You could enable extended tracing to get an idea of the code mechanisms.)

      Comment by Jonathan Lewis — December 10, 2013 @ 9:26 am BST Dec 10,2013 | Reply

      • Jonathan,

        I’m sorry for late reply.

        I think dbms_comparison is not part of a licenced option.
        (Because It’s NOT listed in manual of “Oracle Database Licensing Information 11g Release 2 (11.2) – Oracle Database Options”.)
        Maybe the difference by Version…

        Comment by いのうえけんご (@kengo1255) — December 18, 2013 @ 3:28 am BST Dec 18,2013 | Reply

        • Kengo1255,

          Thanks for the update.
          Please let us know if you spot any significant benefit to one or other package in your testing.

          Comment by Jonathan Lewis — December 18, 2013 @ 10:23 am BST Dec 18,2013

  3. […] posts on comparing data and synchronizing tables. Jonathan Lewis asked for a volunteer to complete his blog post on dbms_rectifier_diff, so I’ll start with that. He has explained briefly the history of the package. I am going to […]

    Pingback by Compare and sync tables: Jonathan Lewis and DBMS_RECTIFIER_DIFF | An Oracle Programmer — February 1, 2014 @ 10:31 am BST Feb 1,2014 | Reply

  4. Hello Jonathan,

    As the pingback just above this indicates, I have finally started blogging and my inaugural post fulfills my promise to “finish” up your post. See http://stewashton.wordpress.com/2014/02/01/compare-and-sync-tables-jonathan-lewis-and-dbms_rectifier_diff/
    I will be doing dbms_comparison next.

    Comment by stewashton — February 1, 2014 @ 10:41 am BST Feb 1,2014 | Reply

  5. […] final followup of Jonathan Lewis’ Differences post looks at the DBMS_COMPARISON package. I have my doubts about this package, which is why I added the […]

    Pingback by Compare and sync tables: DBMS_COMPARISON? | An Oracle Programmer — February 15, 2014 @ 11:38 am BST Feb 15,2014 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,906 other followers