DBMS_COMPARISON

I recently noticed that there is an Oracle-supplied package called DBMS_COMPARISON. It’s been around since Oracle 11g.

As the name implies, we can compare and identify differences in the data between remote and local database objects. If there is a data consistency problem between two environments, the package also gives the possibility to correct the data.

DBMS_COMPARISON in Oracle 19c can compare:

• Tables
• Single-table views
• Materialized views
• Synonyms for tables, single-table views, and materialized views

You can compare objects which are of different types.

Let’s have a look at DBMS_COMPARISON.CREATE_COMPARISON

DBMS_COMPARISON.CREATE_COMPARISON(
   comparison_name      IN  VARCHAR2,
   schema_name          IN  VARCHAR2,
   object_name          IN  VARCHAR2,
   dblink_name          IN  VARCHAR2,
   index_schema_name    IN  VARCHAR2  DEFAULT NULL,
   index_name           IN  VARCHAR2  DEFAULT NULL,
   remote_schema_name   IN  VARCHAR2  DEFAULT NULL,
   remote_object_name   IN  VARCHAR2  DEFAULT NULL,
   comparison_mode      IN  VARCHAR2  DEFAULT CMP_COMPARE_MODE_OBJECT,
   column_list          IN  VARCHAR2  DEFAULT '*',
   scan_mode            IN  VARCHAR2  DEFAULT CMP_SCAN_MODE_FULL,
   scan_percent         IN  NUMBER    DEFAULT NULL,
   null_value           IN  VARCHAR2  DEFAULT CMP_NULL_VALUE_DEF,
   local_converge_tag   IN  RAW       DEFAULT NULL,
   remote_converge_tag  IN  RAW       DEFAULT NULL,
   max_num_buckets      IN  NUMBER    DEFAULT CMP_MAX_NUM_BUCKETS,
   min_rows_in_bucket   IN  NUMBER    DEFAULT CMP_MIN_ROWS_IN_BUCKET);

comparision_name, schema_name,object_name and dblink_name are mandatory. If we only want to compare local schema(s) objects we can set the dblink_name parameter to null.

In my simple example I will just do that.

I will start creating two tables which are identical besides the name, in the same database schema.

create table original(col1 varchar2(30),col2 number);
Table ORIGINAL created.

insert into original values('original_value',1);
1 row inserted.

create table copy_of_original as select * from original;
Table COPY_OF_ORIGINAL created.

commit;
Commit complete.

Next I want to create a comparsion between the two tables.

begin
  DBMS_COMPARISON.CREATE_COMPARISON(
   comparison_name=>'my_comp',
   schema_name =>'myschema',
   object_name=>'original',
   dblink_name=> null,
   remote_schema_name=>'myschema',
   remote_object_name=>'copy_of_original');
end;
ORA-23676: no eligible index on local table "MYSCHEMA"."ORIGINAL"

According to the docs, to create a valid comparision.

For the scan modes CMP_SCAN_MODE_FULL and CMP_SCAN_MODE_CUSTOM to be supported, 

the database  objects must have one of the following types of indexes:

• A single-column index on a number, timestamp, interval, DATE, VARCHAR2, or CHAR datatype column
• A composite index that only includes number, timestamp, interval, DATE, VARCHAR2, or CHAR columns. 
• Each column in the composite index must either have a NOT NULL constraint or be part of the primary key.

But also

The index columns in a comparison must uniquely identify every row involved in a comparison. 

The following constraints satisfy this requirement:

• A primary key constraint
• A unique constraint on one or more non-NULL columns

So I my case I have to add primary keys constraints to my tables.

alter table original add constraint pk_original primary key(col1);
Table ORGINAL altered.

alter table copy_of_original add constraint pk_copy_of_original primary key(col1);
Table COPY_OF_ORGINAL altered.

begin
  DBMS_COMPARISON.CREATE_COMPARISON(
   comparison_name=>'my_comp',
   schema_name =>'myschema',
   object_name=>'original',
   dblink_name=> null,
   remote_schema_name=>'myschema',
   remote_object_name=>'copy_of_original');
end;
PL/SQL procedure successfully completed.

So now I have created a comparison called my_comp which compares the table “original” and “copy_of_original”.

The next step is to run DBMS_COMPARISON.COMPARE

DECLARE
  consistent   BOOLEAN;
  scan_info    DBMS_COMPARISON.COMPARISON_TYPE;
BEGIN
  consistent := DBMS_COMPARISON.COMPARE
                ( comparison_name => 'my_comp'
                , scan_info       => scan_info
                , perform_row_dif => TRUE
                );
  DBMS_OUTPUT.PUT_LINE('Scan ID: '||scan_info.scan_id);
  IF consistent=TRUE THEN
    DBMS_OUTPUT.PUT_LINE('No differences were found.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Differences were found.');
  END IF;
END;
/
Scan ID: 10
No differences were found.



PL/SQL procedure successfully completed..

No differences, obviously . Let’s update the tabel “copy_of_original”, and rerun the scan.

update copy_of_original set col1='copy_of_original';
1 row updated.

commit;
Commit complete.

DECLARE
  consistent   BOOLEAN;
  scan_info    DBMS_COMPARISON.COMPARISON_TYPE;
BEGIN
  consistent := DBMS_COMPARISON.COMPARE
                ( comparison_name => 'my_comp'
                , scan_info       => scan_info
                , perform_row_dif => TRUE
                );
  DBMS_OUTPUT.PUT_LINE('Scan ID: '||scan_info.scan_id);
  IF consistent=TRUE THEN
    DBMS_OUTPUT.PUT_LINE('No differences were found.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Differences were found.');
  END IF;
END;
/
Scan ID: 12
Differences were found.

Scan id 12 is the parent scan id for every other scan in THIS specified comparison. Each time the DBMS_COMPARISON.COMPARE function is executed, one or more new scans may be performed, but with one common parent scan id.

In the toolbox we have several dictonary views that are available. Depending on your needs and priviliges of course.

• USER_COMPARISON
• USER_COMPARISON_COLUMNS
• USER_COMPARISON_SCAN_SUMMARY
• USER_COMPARISON_COLUMNS
• USER_COMPARISON_ROW_DIF
• USER_COMPARISON_SCAN
• USER_COMPARISON_SCAN_VALUES

To show the difference between the two tables for scan id 12

select
    c.column_name,
    r.index_value,
    decode(r.local_rowid, NULL, 'No', 'Yes')  local_rowid,
    decode(r.remote_rowid, NULL, 'No', 'Yes') remote_rowid
from
    user_comparison_columns c,
    user_comparison_row_dif r,
    user_comparison_scan    s
where c.comparison_name = 'MY_COMP'
    and r.scan_id = s.scan_id
    and s.parent_scan_id = 12
    and r.status = 'DIF'
    and c.index_column = 'Y'
    and c.comparison_name = r.comparison_name;

Here we see that the remote value for COL1 is “copy_of_original” and the local value is “original_value”. There is of course many ways to compare data in Oracle, but this was a new way for me.

DBMS_COMPARISON.CONVERGE gives the possibilty to make the data consistent between two source. That’s another post.

If you are planning to use DBMS_COMPARISON on a bigger scale, I would recommend reading about potential hash collisions.

Over & Out