Playing with DBMS_COMPARISON package in 11G

One of a good feature in 11G is the ability to compare and converge the data with the DBMS_COMPARISON package, The DBMS_COMPARISON package provides interfaces to compare and converge database objects at different databases. The best part is we can compare tables with different names and columns also provided the data type is same, also it is possible to compare a table with a materialzied view.

Some prerequisites are there in order to use this package:

1. The source database must be an Oracle 11g Release 1 or later.

2. The destination database must be an Oracle database.

3. The character set must be the same.

  1  BEGIN
  2    DBMS_COMPARISON.CREATE_COMPARISON
  3    ( comparison_name => 'compare_t1_and_t22'
  4    , schema_name     => 'scott'
  5    , object_name     => 'T1'
  6    , dblink_name     => null
  7    , remote_schema_name=> 'SCOTT'
  8    , remote_object_name=>'T2'
  9    );
 10* END;
SQL> /
BEGIN
*
ERROR at line 1:
ORA-23626: No eligible index on table SCOTT.T1
ORA-06512: at "SYS.DBMS_COMPARISON", line 4793
ORA-06512: at "SYS.DBMS_COMPARISON", line 448
ORA-06512: at line 2

## Lets see what error explains :-

[oracle@node4 ~]$ oerr ora 23626
23626, 00000, "No eligible index on table %s.%s"
// *Cause:  Table did not have an eligible index to use for comparison.
// *Action: For a table to be compared using DBMS_COMPARISON, the table must
//          have an appropriate index on a column of certain data types.
//          Check the documentation for supported data types.


## So we need an index on the column but it should be the primary key one not the non unique one

  1  BEGIN
  2    DBMS_COMPARISON.CREATE_COMPARISON
  3    ( comparison_name => 'compare_t1_and_t22'
  4    , schema_name     => 'scott'
  5    , object_name     => 'T1'
  6    , dblink_name     => null
  7    , remote_schema_name=> 'SCOTT'
  8    , remote_object_name=>'T2'
  9    );
 10* END;
SQL> /

PL/SQL procedure successfully completed.

## Let us now compare both the objects


  1  DECLARE
  2    consistent   BOOLEAN;
  3    scan_info    DBMS_COMPARISON.COMPARISON_TYPE;
  4  BEGIN
  5    consistent := DBMS_COMPARISON.COMPARE
  6                  ( comparison_name =>'compare_t1_and_t22'
  7                  , scan_info       => scan_info , perform_row_dif => TRUE);
  8    DBMS_OUTPUT.PUT_LINE('Scan ID: '||scan_info.scan_id);
  9    IF consistent=TRUE THEN
 10      DBMS_OUTPUT.PUT_LINE('No differences were found.');
 11    ELSE
 12      DBMS_OUTPUT.PUT_LINE('Differences were found.');
 13    END IF;
 14* END;
SQL> /
Scan ID: 1
Differences were found.

PL/SQL procedure successfully completed.

## So there were differences found, let us see them in below query


  1  SELECT s.scan_id
  2  ,      c.COMPARISON_NAME
  3  ,      c.SCHEMA_NAME
  4  ,      c.OBJECT_NAME
  5  ,      s.CURRENT_DIF_COUNT
  6  FROM   USER_COMPARISON c
  7  ,      USER_COMPARISON_SCAN_SUMMARY s
  8  WHERE  c.COMPARISON_NAME = s.COMPARISON_NAME
  9  AND    s.scan_id =
 10* 1
SQL> /

   SCAN_ID COMPARISON_NAME                SCHEMA_NAME                    OBJECT_NAME                    CURRENT_DIF_COUNT
---------- ------------------------------ ------------------------------ ------------------------------ -----------------
         1 COMPARE_T1_AND_T22             SCOTT                          T1                                             2

## And lets see the values


  1  SELECT c.COLUMN_NAME
  2  ,      r.INDEX_VALUE
  3  ,      case
  4         when r.LOCAL_ROWID is null
  5         then 'No'
  6         else 'Yes'
  7         end  LOCAL_ROWID
  8  ,      case
  9         when r.REMOTE_ROWID is null
 10         then 'No'
 11         else 'Yes'
 12         end  REMOTE_ROWID
 13  FROM   USER_COMPARISON_COLUMNS c
 14  ,      USER_COMPARISON_ROW_DIF r
 15  ,      USER_COMPARISON_SCAN s
 16  WHERE  c.COMPARISON_NAME = 'COMPARE_T1_AND_T22'
 17  AND    r.SCAN_ID         = s.SCAN_ID
 18  AND    r.STATUS          = 'DIF'
 19  --AND    c.INDEX_COLUMN    = 'Y'
 20  AND    c.COMPARISON_NAME = r.COMPARISON_NAME
 21  ORDER
 22* BY     r.INDEX_VALUE
SQL> /

COLUMN_NAME                    INDEX_VALUE                    LOC REM
------------------------------ ------------------------------ --- ---
C2                             1                              Yes No
ID                             1                              Yes No
C2                             2                              No  Yes
ID                             2                              No  Yes


## Lets now do the converging part by making sure local wins


  1  DECLARE
  2    scan_info    DBMS_COMPARISON.COMPARISON_TYPE;
  3  BEGIN
  4    DBMS_COMPARISON.CONVERGE
  5    ( comparison_name  => 'compare_t1_and_t22'
  6    , scan_id          => 1
  7    , scan_info        => scan_info
  8    , converge_options => DBMS_COMPARISON.CMP_CONVERGE_LOCAL_WINS
  9    );
 10    DBMS_OUTPUT.PUT_LINE('Local Rows Merged: '||scan_info.loc_rows_merged);
 11    DBMS_OUTPUT.PUT_LINE('Remote Rows Merged: '||scan_info.rmt_rows_merged);
 12    DBMS_OUTPUT.PUT_LINE('Local Rows Deleted: '||scan_info.loc_rows_deleted);
 13    DBMS_OUTPUT.PUT_LINE('Remote Rows Deleted: '||scan_info.rmt_rows_deleted);
 14* END;
 15  /
Local Rows Merged: 0
Remote Rows Merged: 1
Local Rows Deleted: 0
Remote Rows Deleted: 1

PL/SQL procedure successfully completed.