Table Comparison and Synchronisation

If you are managing more than one databases for same application, it is very common that you need to compare one table ( say scott.employee) in two different databases. I think, we had previous experiences to do such job :) . The simplest way is just minus two tables. Like

1. Two different tables In same DB - SELECT * FROM table1 minus SELECT * FROM table2;
2. Same table in two different DB - SELECT * FROM table1 minus SELECT * FROM table2@remote_db;

But this simple task can be time consuming if your tables are very big (say 1000000+ records). If targeted tables do not contain BLOB,CLOB or Long data type columns, you can easily use DBMS_COMPARISON, a new package introduce in Oracle 11g, to compare them. More over this package also provide Synchronization to remove miss match records in targeted tables. For details please go through Oracle Documentations.

Here we are going to compare and synchronise 'employee' table in two different DB.

Requirement: A database link between host (i.e, where these script will be run) and remote DB.

Step One: Create comparison Task

BEGIN
DBMS_COMPARISON.CREATE_COMPARISON(
comparison_name => 'compare_employee'
, schema_name => 'SCOTT'
, object_name => 'EMPLOYEE'
, dblink_name => 'db_link_to_remote'
, remote_schema_name=>'SCOTT'
, remote_object_name=>'EMPLOYEE'
);
END;
/

If you wish to compare two different table in same DB, set dblink_name => NULL, object_name => 'table1' and remote_object_name=>'table2'

Step Two: Execute comparison Task

DECLARE
consistent BOOLEAN;
scan_info DBMS_COMPARISON.COMPARISON_TYPE;
BEGIN
consistent := DBMS_COMPARISON.COMPARE( comparison_name =>'compare_employee', 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 found.');
ELSE
DBMS_OUTPUT.PUT_LINE('Differences were found.');
END IF;
END;
/

Step Three: See how may difference you got

col COMPARISON_NAME format a15;
col SCHEMA_NAME format a15;
col OBJECT_NAME format a15;


SELECT s.scan_id ,c.COMPARISON_NAME,c.SCHEMA_NAME,c.OBJECT_NAME ,s.CURRENT_DIF_COUNT
FROM USER_COMPARISON c,USER_COMPARISON_SCAN_SUMMARY s
WHERE c.COMPARISON_NAME = s.COMPARISON_NAME AND s.scan_id = 514; (this number is a out put of step two)


Step Four: Synchronise Remote table with host table.

DECLARE
scan_info DBMS_COMPARISON.COMPARISON_TYPE;
BEGIN
DBMS_COMPARISON.CONVERGE (comparison_name => 'compare_employee',
scan_id => 506,
scan_info => scan_info, --this number is a out put of step two
converge_options => DBMS_COMPARISON.CMP_CONVERGE_LOCAL_WINS);
DBMS_OUTPUT.PUT_LINE('Local Rows Merged: '||scan_info.loc_rows_merged);
DBMS_OUTPUT.PUT_LINE('Remote Rows Merged: '||scan_info.rmt_rows_merged);
DBMS_OUTPUT.PUT_LINE('Local Rows Deleted: '||scan_info.loc_rows_deleted);
DBMS_OUTPUT.PUT_LINE('Remote Rows Deleted: '||scan_info.rmt_rows_deleted);
END;
/

You can also Synchronise Host table by using Remote table, please visit given link.

I hope you all will enjoy this exiting new package of Oracle 11g.