Identifying “Missing” Data

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.

Categories: UncategorizedTags: , , , ,

Leave a Reply

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

You are commenting using your 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


My Experiences with Oracle Technology


If travel is an addiction, I'm afflicted.

The Black Veil Ones

My Diary Travel, Photography, My Sight About Somethings

Off to Ireland

Trotz Knacks den Schritt ins Ausland wagen

Randy Johnson's Oracle Blog

Oracle DBA Topics Including Unix, Scripting, & Automation

Model For {Change

Creating a positive change.


Le Bonnet voyageur • The travelling Winter Hat

Another Photography Enthusiast



Adventures in food and drink

Break Your Cage

Embracing The Unknown - Travel is not a pause from life, merely an extension of it.

My Girls On Tour

Fashion & Travel Blog by Abigail Royston


Two college girls trying to make the most out of living in NYC without breaking the bank!

Don Charisma

because anything is possible with Charisma

Bunny Kitchen

Exploring the possibilities of cruelty free food

Angelart Star

The beautiful picture of angels makes you happy.

%d bloggers like this: