Oracle DBMS_COMPARISON package overview

During replicating data in one or another way, it happens that synchronized objects may differ. This situation may take place in the following cases:

  • If changes made in one of copied databases were not captured
  • The changes were captured but not forwarded
  • The changed were forwarded but not applied due to some reasons

It should be admitted that full or partial synchronization may be required due to some other reasons, not related to replication. But the question is how to synchronize database objects?

In case of replication, you can try to initialize objects all over again, however it is not always acceptable. To define such problems, Oracle Database 11g has introduced integrated DBMS_COMPARISON package that supports a limited set of data types:

VARCHAR2, NVARCHAR2, NUMBER, FLOAT, DATE, BINARY_FLOAT, BINARY_DOUBLE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND, RAW, CHAR, NCHAR.

DBMS_COMPARISON can be used on conditions that:

  • At least one of compared Oracle servers has version 11.
  • The second Oracle server has version 10.2 or higher.
  • Such objects as tables, views with a single table, materialized view, synonyms
  • The Oracle servers have the same encoding.

Now let’s examine how DBMS_COMPARISON package works.
Here are the scripts to create test databases to illustrate the case.

Database 1:

Database 2:

Now we are ready to compare and then synchronize the databases. Let’s connect to database 1, create a comparison for table test_user.departments (we will repeat these actions for table test_user.employees).

Now launch the comparison:

Result:

Scan ID may differ from 1. This means that the comparison was not the first on the current Oracle server. It’s good to remember this value as it will be useful. Differences in our case were found, so we can take a good look at them.

There are two queries stated below, the first will show how many differences are in two tables, the second will show the list of rows that differ.

Query 1 (how many differences are in two tables):

Result:

Query 2 (the list of rows that differ):

Result:

After looking through the differences and ensuring that they are exactly those we want to synchronize, now is the time to synchronize them.

Result:

Note that CMP_CONVERGE_LOCAL_WINS parameter means that data on the local server will be considered as etalon, and CMP_CONVERGE_REMOTE_WINS means that data on the remote server will be etalon.

Leave a Comment