The DBMS_COMPARISON package is a powerful tool to synchronize data between distributed databases. This article shows you how to use DBMS_COMPARISON to evaluate and fix data differences. You examine how to create a comparison, compare the results, and converge the results. Converge leaves something to be desired as a description – what it does is to synchronize the data that has become different over time.

Testing the DBMS_COMPARISON package concepts can be hard because most developers don’t have two databases that they can play with on a single laptop or desktop computer. To solve that problem, you will use a DB_LINK to mimic a second database.

This article has three parts:

  • Configuring the test environment
  • Comparing the tables
  • Converging the tables’ data 

If you’re familiar with how a DB_LINK works, you only need to glance over the material and copy the code from the first section. The comparing and converging sections will require a slower and more focused read.

Configuring the Test Environment

You configure the test environment by creating and seeding two tables, modifying your tnsnames.ora file to add a loopback entry, and creating a loopback database link. There are five substeps required to create and seed the two tables.

Creating and Seeding Two Tables

The first step requires us to create a table that you can synchronize. You can create your own table or a film1 table like my example to keep it simple and easy to work with. The film1 table for this example is:

SQL> CREATE TABLE film1

  2  ( film_id      NUMBER PRIMARY KEY

  3  , film_name    VARCHAR2(40)

  4  , release_date DATE

  5  , studio       VARCHAR2(30));

You should take note that tables require primary key constraints when you want to use the DBMS_COMPARISON package. Line 2 declares an unnamed primary key constraint for the film1 table. As a reminder of best practice, you should always use named primary key constraints.

You then create the film2 table:

SQL> CREATE TABLE film2

  2  ( film_id      NUMBER PRIMARY KEY

  3  , film_name    VARCHAR2(40)

  4  , release_date DATE

  5  , studio       VARCHAR2(30));

The second step requires you create a film1_s sequence for the film1 table, like

SQL> CREATE SEQUENCE film1_s;

The third step requires you to insert data for the following films into the film1 table:

Mission: Impossible, 22-MAY-96, Paramount Pictures

Mission: Impossible II, 26-MAY-00, Paramount Pictures

Mission: Impossible III, 05-MAY-06, Paramount Pictures

Mission: Impossible - Ghost Protocol, 07-DEC-11, Paramount Pictures

Mission: Impossible - Rogue Nation, 23-JUL-15, Paramount Pictures

After you insert the five movies in the film1 table, the fourth step requires you to insert a copy of the data from the film1 table into the film2 table. You can use the following statement to transfer the data:

SQL> INSERT INTO film2

  2    SELECT * FROM film1;

The fifth step requires you to change the data in the film2 table. You can do that with the following UPDATE statement:

SQL> UPDATE film2

  2  SET    film_name = REGEXP_REPLACE(film_name, ' - ', ' ', 1, 1,'i')

  3  WHERE  REGEXP_LIKE(film_name,' - ','i');

After creating and seeding the two tables, you should verify whether it’s complete. You can do that the following query:

SQL> COLUMN film_name1 FORMAT A37 HEADING "Film Name 1"

SQL> COLUMN film_name2 FORMAT A37 HEADING "Film Name 2"

SQL> SELECT f1.film_name AS film_name1

  2  ,          f2.film_name AS film_name2

  3  FROM   film1 f1 INNER JOIN film2 f2

  4  ON     f1.film_id = f2.film_id;

It should display:

Film Name 1                   Film Name 2

------------------------------------- -------------------------------------

Mission: Impossible                   Mission: Impossible

Mission: Impossible                   Mission: Impossible

Mission: Impossible II                Mission: Impossible II

Mission: Impossible III               Mission: Impossible III

Mission: Impossible - Ghost Protocol  Mission: Impossible Ghost Protocol

Mission: Impossible - Rogue Nation    Mission: Impossible Rogue Nation

The last two rows of the film2 table no longer have their hyphen. The test tables and data are now complete.

Modifying your tnsnames.ora file

If you’re new to database administration, the tnsnames.ora file maps a path to you’re your database. Oracle requires this mapping for sqlplus and any tool that you use. You can find the tnsnames.ora file in the $ORACLE_HOME/network/admin directory. Assuming you have an entry like this:

XE =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = XE)

    )

  )

You can simply copy below that entry and change it’s name from XE to LOOPBACK, like:

LOOPBACK =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = XE)

    )

  )

You can’t test this quite yet in SQL but you can test it from the command line or SQL Developer by trying to create a connection through the TNS alias LOOPBACK. If you can do that, the step is complete.

Creating a LOOPBACK Database Link

There are two options available for creating the database link. One option requires that your local schema, in this case the student schema or user, have the privilege to create a database link. If they do, you can create a LOOPBACK database link like this:

SQL> CREATE DATABASE LINK loopback

  2    CONNECT TO student IDENTIFIED BY student

  3    USING 'loopback';

If your student user doesn’t have the privileges, you can connect as the system user and create a public database link. The syntax to do that is:

SQL> CREATE PUBLIC DATABASE LINK loopback

  2    CONNECT TO student IDENTIFIED BY student

  3    USING 'loopback';

You can test the database link by querying the film2 table like this:

SQL> SELECT film_name FROM film2@LOOPBACK;

Congratulations, you configured your test environment. Next, you check for the presence of the DBMS_COMPARISON package, and write an anonymous block PL/SQL program that creates and compares records in both tables.

Comparing the Tables

In this section, you learn how to create and manage a comparison and how to compare result sets. The first thing you need to do is check whether the DBMS_COMPARISON package exists in your environment. You can do that by typing the following at a SQL> prompt or from SQL Developer or Toad for Oracle:

SQL> DESC dbms_comparison

The DBMS_COMPARISON package is present when sqlplus or your tool displays the details of the package. In the rare case where the package isn’t installed, you can connect as the system user and run the following two commands to create the package:

SQL> @?/rdbms/admin/dbmscmp.sql

SQL> @?/rdbms/admin/prvtcmp.plb

Assuming you’ve got the DBMS_COMPARISON package installed, you need to know that each comparison requires a unique name. You can use whatever comparison name you like but the example uses COMPARE_NAME.

You also may need to grant privileges to the student schema with the SYSDBA role. The two roles are these:

SQL> GRANT EXECUTE ON dbms_comparison TO student;

SQL> GRANT SELECT ON dba_comparison_row_dif TO student;

After granting the privileges to the student user, you should check the

SQL> SELECT comparison_name

  2  ,      scan_id

  3  ,      status

  4  FROM   dba_comparison_row_dif;

At this point, the query should return no rows because you haven’t compared any rows.

The following anonymous block compares the two tables, and reports whether there are differences or not:

SQL> DECLARE

  2    /* Declare local variables. */

  3    lv_compare_results  DBMS_COMPARISON.COMPARISON_TYPE;

  4    lv_difference       BOOLEAN;

  5  BEGIN

  6    /* Create comparison. */

  7    dbms_comparison.create_comparison(

  8     comparison_name => 'COMPARISON_NAME'

  9   , schema_name => 'student'

 10   , object_name => 'FILM1'

 11   , dblink_name => 'loopback'

 12   , remote_schema_name => 'student'

 13   , remote_object_name => 'FILM2');

 14 

 15    /* Check for difference. */

 16    lv_difference :=

 17   dbms_comparison.compare(

 18      'COMPARISON_NAME'

 19     , lv_compare_results

 20     , null

 21     , null

 22     , TRUE);

 23 

 24    /* Print messages for not found and found. */

 25    IF lv_difference THEN

 26   dbms_output.put_line('None found.');

 27    ELSE

 28   dbms_output.put_line('Difference found.');

 29    END IF;

 30  END;

 31  /

After comparing rows, you rerun the previous query:

SQL> SELECT comparison_name

  2  ,      scan_id

  3  ,      status

  4  FROM   dba_comparison_row_dif;

It should return the following:

COMPARISON_NAME           SCAN_ID STA

------------------------------ ---------- ---

COMPARISON_NAME                12 DIF

COMPARISON_NAME                12 DIF

The query results match the two rows that differ.

Converging the Tables’ Data

Now that you know there are differences between the two data sets, you can converge or fix the data. The following anonymous block converges the data:

SQL> DECLARE

  2    /* Declare local variables. */

  3    lv_compare_results  DBMS_COMPARISON.COMPARISON_TYPE;

  4    lv_counter    NUMBER := 0;

  5    lv_difference       BOOLEAN;

  6    lv_message    VARCHAR2(10);

  7 

  8    /* Declare a switch back cursor. */

  9    CURSOR switch_back IS

 10       SELECT comparison_name

 11       ,      scan_id

 12       ,      status

 13       FROM   dba_comparison_row_dif;

 14 

 15  BEGIN

 16    /* Read through the switch back. */

 17    FOR i IN switch_back LOOP

 18     dbms_comparison.converge(

 19          comparison_name => 'COMPARISON_NAME'

 20        , scan_id => i.scan_id

 21        , scan_info => lv_compare_results

 22        , converge_options => DBMS_COMPARISON.CMP_CONVERGE_LOCAL_WINS

 23        , perform_commit => TRUE

 24        , local_converge_tag => null

 25        , remote_converge_tag => null);

 26 

 27   /* Recheck comparison. */

 28   lv_difference := dbms_comparison.recheck(

 29                        'COMPARISON_NAME'

 30                       , i.scan_id

 31                       , TRUE);

 32 

 33   /* Check for a difference. */

 34   IF lv_difference THEN

 35     dbms_output.put_line('Scan ID ['||i.scan_id||'] is the same.');

 36   ELSE

 37     dbms_output.put_line('Scan ID ['||i.scan_id||'] is different.');

 38     lv_counter := lv_counter + 1;

 39   END IF;

 40    END LOOP;

 41  END;

 42  /

The anonymous block defines a local lv_compare_results variable on line 3. The variable uses the COMPARISON_TYPE  data type. The COMPARISON_TYPE data type is a PL/SQL record data type in the DBMS_COMPARISON package.

The call to the converge procedure on lines 18 through 25 fixes only one row of data at a time. The call to the recheck function on line 28 to 31 returns true when there are no other rows to change.

You can run the following query to verify the data matches:

SQL> COLUMN film_name1 FORMAT A37 HEADING "Film Name 1"

SQL> COLUMN film_name2 FORMAT A37 HEADING "Film Name 2"

SQL> SELECT f1.film_name AS film_name1

  2  ,      f2.film_name AS film_name2

  3  FROM   film1 f1 INNER JOIN film2 f2

  4  ON     f1.film_id = f2.film_id;

It should display:

Film Name 1                   Film Name 2

------------------------------------- -------------------------------------

Mission: Impossible                   Mission: Impossible

Mission: Impossible                   Mission: Impossible

Mission: Impossible II                Mission: Impossible II

Mission: Impossible III               Mission: Impossible III

Mission: Impossible - Ghost Protocol  Mission: Impossible - Ghost Protocol

Mission: Impossible - Rogue Nation    Mission: Impossible - Rogue Nation

The results show that you that the anonymous block successfully converged data. Now you need to remove the comparison of the data, which you do with the following command:

SQL> EXECUTE dbms_comparison.drop_comparison('COMPARISON_NAME');

That call to the drop_comparison procedure cleans up the comparison and completes the data convergence.  Through these steps you’ve learned how to fix differences in distributed data.