dbms_comparison

To start with, many of the times we need to compare to the schema may be on same DB or on Remote. As of example, when struggling in Oracle Replication, Streams or many times in Golden Gate. To compare, many including me used for application like TOAD but there are some clients which won’t allowed for TOAD. So for them ORACLE has provided “DBMS_COMPARISON” that will not just compare the two objects but also can used to Synchronizes the database objects previously compared.

Requirements & limitations :

When running DBMS_COMPARISON, the local comparison database where the call to DBMS_COMPARISON is being made must be an Oracle 11g database. The remote database can be an Oracle 10g Release one or later. Also the database character sets on both databases must be the same.

DBMS_COMPARISON package supports all Oracle Data Types except LOB,CLOB,BLOB,LONG, RAW, ROWID etc.

DBMS_COMPARISON also supports different scan modes.

CMP_SCAN_MODE_FULL: The full database object is compared.

CMP_SCAN_MODE_RANDOM: The object is randomly scanned for the comparison.

CMP_SCAN_MODE_CYCLIC: Only a portion of the database object is scanned.

So now using the DBMS_COMPARISON.

To perform comparison, we need to perform few steps:

Create a comparison

Execute comparison & see the result

Converge the data if desired

To start with, I will create dblink between source & remote database i.e. target.

SQL> select 'dbms_comparison' from dual;

'DBMS_COMPARISO                                                                

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

dbms_comparison                                                                

SQL> show user

USER is "SYS"

SQL>

SQL> CREATE DATABASE LINK dbms connect to jay identified by jay using 'target';

Database link created.

SQL> SELECT * FROM DUAL@dbms;

D                                                                              

-                                                                              

X

Also to add, index must be present to use this package.

SQL> conn jay/jay

Connected.

SQL> create table emp   (a number primary key,  b varchar2(22));

Table created.

SQL> insert into emp  values(1, 'Bilal');

1 row created.

SQL>  insert into emp   values(2,'Ferdous');

1 row created.

SQL> commit;

Commit complete.

Also creating table on target DB

SQL> conn jay/jay@target

Connected.

SQL> create table emp   (a number primary key,  b varchar2(22));

Table created.

Now creating comparison:

SQL> conn / as sysdba

Connected.

SQL> BEGIN

  2  DBMS_COMPARISON.CREATE_COMPARISON  ( comparison_name => 'emp_replication',  schema_name=> 'jay',object_name => 'emp' ,dblink_name =>'dbms' , remote_schema_name=>'jay', remote_object_name=>'emp');

  3  END;

  4   /

PL/SQL procedure successfully completed.

Now executing the comparison and looking for any difference:

SQL> set serveroutput on

SQL>  DECLARE

  2    consistent   BOOLEAN;

  3    scan_info    DBMS_COMPARISON.COMPARISON_TYPE;

  4   BEGIN  consistent := DBMS_COMPARISON.COMPARE( comparison_name => 'emp_replication',

  5           scan_info => scan_info, perform_row_dif => TRUE);

  6           DBMS_OUTPUT.PUT_LINE('Scan ID: '||scan_info.scan_id);

  7   IF     consistent=TRUE THEN

  8            DBMS_OUTPUT.PUT_LINE('No differences were found.');

  9   ELSE

 10            DBMS_OUTPUT.PUT_LINE('Differences were found.');

 11   END IF;

 12   END;

 13  /

Scan ID: 1                                                                     

Differences were found.                                                        

PL/SQL procedure successfully completed.

Now since we have difference in data, we can use dbms_comparison to synch the data.

SQL> DECLARE

  2     scan_info    DBMS_COMPARISON.COMPARISON_TYPE;

  3  BEGIN

  4     DBMS_COMPARISON.CONVERGE( comparison_name  => 'emp_replication',

  5           scan_id => 1 , scan_info => scan_info, converge_options => DBMS_COMPARISON.CMP_CONVERGE_LOCAL_WINS);

  6           DBMS_OUTPUT.PUT_LINE('Local Rows Merged: '||scan_info.loc_rows_merged);

  7           DBMS_OUTPUT.PUT_LINE('Remote Rows Merged: '||scan_info.rmt_rows_merged);

  8           DBMS_OUTPUT.PUT_LINE('Local Rows Deleted: '||scan_info.loc_rows_deleted);

  9           DBMS_OUTPUT.PUT_LINE('Remote Rows Deleted: '||scan_info.rmt_rows_deleted);

 10  END;

 11   /

Local Rows Merged: 0                                                           

Remote Rows Merged: 2                                                          

Local Rows Deleted: 0                                                           

Remote Rows Deleted: 0                                                         

PL/SQL procedure successfully completed.

SQL> conn jay/jay@target

Connected.

SQL> select * from emp;

         A B                                                                    

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

         1 Bilal                                                               

         2 Ferdous 

If you forget the SCAN_ID you can use the following SQL to find it:

SELECT DISTINCT ROOT_SCAN_ID

FROM DBA_COMPARISON_SCAN_SUMMARY

WHERE COMPARISON_NAME = 'QUEST_COMPARE';

Review the Results of the Comparison

SELECT a.owner, a.comparison_name, a.schema_name, a.object_name,

z.current_dif_count difference

FROM dba_comparison a, dba_comparison_scan_summary z

WHERE a.comparison_name=z.comparison_name

AND a.owner=z.owner

AND z.scan_id=& scan_id;

You can use this query to product a list of all comparisons:

SELECT OWNER,

       ,COMPARISON_NAME

       ,SCHEMA_NAME

       ,OBJECT_NAME

       ,OBJECT_TYPE

       ,SCAN_MODE

       ,DBLINK_NAME

  FROM DBA_COMPARISON;