Use of DBMS_COMPARISON for Data sync

Use of DBMS_COMPARISON for Data sync:

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


Oracle 11g has provided us a wonderful package named DBMS_COMPARISION which can be used to sync table data .



The only requirement here is both the source and target tables should have atleast one index on them.



Please check the steps below



12:26:31 SQL> select count(1) from EMP;



COUNT(1)

----------

5



1 row selected.



The table EMP_BKP has same structure of EMP, but does not have any rows.



12:29:25 SQL> select count(1) from EMP_BKP;



COUNT(1)

----------

0



1 row selected.



Now to sync this data, we use DBMS_COMPARISION,



exec dbms_comparison.create_comparison('COMP_VIJAY','VIJAY','EMP_BKP',null,null,null,'VIJAY','EMP');



The above is just to let oracle know we are doing a comparision of the above schemas.



declare

compare_info dbms_comparison.comparison_type;

compare_return boolean;

begin

compare_return :=dbms_comparison.compare (comparison_name=>'COMP_VIJAY',scan_info=>compare_info,perform_row_dif=>TRUE);

if compare_return=TRUE

then

dbms_output.put_line('the tables have same Data.');

else

dbms_output.put_line('Table data is not same.');

dbms_output.put_line('Check the dba_comparison and dba_comparison_scan_summary views for locate the differences for scan_id:'

compare_info.scan_id);

end if;

end;

/

The PL/SQL block will tell you if the table datas are same or not. If not same, then it gives the scan_id which can be used to query some of the dictionary views.



Now use the above scan_id in the below query . For example lets say scan_id is 9 then



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=9;



This will give you more details of the data changes.



Now to sync them use the below PL/SQL block



declare

compare_info dbms_comparison.comparison_type;

begin

dbms_comparison.converge (comparison_name=>'COMP_VIJAY',scan_id=>9,scan_info=>compare_info,converge_options=>dbms_comparison.cmp_converge_remote_wins);

dbms_output.put_line('--- Results ---');

dbms_output.put_line('Local rows Merged by process: '

compare_info.loc_rows_merged);

dbms_output.put_line('Remote rows Merged by process: '

compare_info.rmt_rows_merged);

dbms_output.put_line('Local rows Deleted by process: '

compare_info.loc_rows_deleted);

dbms_output.put_line('Remote rows Deleted by process: '

compare_info.rmt_rows_deleted);

end;

/



This is very useful in data sync and is much faster.



You can also use DBLINK for remote databases. I have just used a local schema and database for my testing purpose.