In researching a replication issue, I thought I’d do some digging around the hairy situation of mismatch data.  From my earlier post today, you’d have seen I had to remove a ton of duplicate rows from a table already; the problem I am facing now is that I have missing and/or changed data between my source and target tables.

If you’re a GoldenGate person, you’d just reinstantiate the target tables which would probably work well, but then it wouldn’t highlight the use of a very nice Oracle procedure.

I’ve created a use case to illustrate this.  I’m using 12c with pluggable databases and I have created a link from SOE@pdb2 to SOE@pdb3.  You need the link for the process to work.

create database link pdb3 connect to soe identified by soe using ‘pdb3’;

set serveroutput on size 1000000

Now I have an ORDERS table in both databases; from one I have deleted all the orders associated with two WAREHOUSE_IDs (11,364 rows).

Let’s set up the process (I’m going to paste the SQL at the bottom of this post).

.

That defines the comparison.  Now I have to run the analysis, referencing this comparison definition.

After a while (depends on your system speed and volumes of data being compared), it will tell you there are differences or not.  The following query shows you the summary of these differences.

Now, we can see the differences themselves with this query.

You’ll see the data differences in detail.  Note that our code has translated the ROWID into a simple yes/no for readability.  The INDEX_VALUE column contains ORDER_IDs.

So, let’s run the converge utility – this will synchronize the source and the target.

At the end, it gives you a summary.  As expected, we got 11,364 rows deleted from the target system.

Local Rows Merged: 0
Remote Rows Merged: 0
Local Rows Deleted: 0
Remote Rows Deleted: 11364

A word of caution.  What happens if you wanted Oracle to pull the 11k deleted rows over instead of deleting them?  Well, that can be done, but you have to set the DBMS_COMPARISON converge options to CMP_CONVERGE_REMOTE_WINS instead of CMP_CONVERGE_LOCAL_WINS.

Unfortunately there does not appear to be a bidirectional option…  That’s an experiment for another day.