DBMS_COMPARISON

DBMS_COMPARISOM

SOURCE:
 1.) create database link to TARGET database:
 create public database link lnk_clonetst connect to clone identified by clone using 'clonetst';

2.) Create table on SOURCE and TARGET system with same name and structure;
 --> must use primary key

create table cmagno.teste   (a number primary key,  b varchar2(22));

3.) Inserting rows on SOURCE System:

insert into cmagno.teste(a,b) values (1,'LINE 1');
 insert into cmagno.teste(a,b) values (2,'LINE 2');
 insert into cmagno.teste(a,b) values (3,'LINE 3');
 COMMIT;

4.) Creating Comparison

On Source:

BEGIN
 DBMS_COMPARISON.CREATE_COMPARISON  ( comparison_name => 'TABLE_TESTE',  schema_name=> 'cmagno',object_name => 'teste' ,dblink_name =>'lnk_clonetst' , remote_schema_name=>'cmagno', remote_object_name=>'teste');
 END;
 /

5.)  SELECT OWNER
 ,COMPARISON_NAME
 ,SCHEMA_NAME
 ,OBJECT_NAME
 ,OBJECT_TYPE
 ,SCAN_MODE
 ,DBLINK_NAME
 FROM DBA_COMPARISON;

OWNER      COMPARISON_NAME                SCHEMA_NAME                    OBJECT_NAME                    OBJECT_TYPE       SCAN_MODE DBLINK_NAME
 ---------- ------------------------------ ------------------------------ ------------------------------ ----------------- --------- --------------------------------------------------------------------------------------------------------------------------------
 SYS        TABLE_TESTE                    CMAGNO                         TESTE                          TABLE             FULL      LNK_CLONETST.KCLX.KNEIP.COM

6.) execute COMPARISON

set serveroutput on
 DECLARE
 V_DIF   BOOLEAN;
 scan_info    DBMS_COMPARISON.COMPARISON_TYPE;
 BEGIN  V_DIF := DBMS_COMPARISON.COMPARE( comparison_name => 'TABLE_TESTE', scan_info => scan_info, perform_row_dif => TRUE);
 DBMS_OUTPUT.PUT_LINE('Scan ID: '||scan_info.scan_id);
 IF     V_DIF=TRUE THEN
 DBMS_OUTPUT.PUT_LINE('NO ROWS DIFFERENT');
 ELSE
 DBMS_OUTPUT.PUT_LINE('DIFFERENCES FOUND...');
 END IF;
 END;
 /

SELECT DISTINCT ROOT_SCAN_ID
 FROM DBA_COMPARISON_SCAN_SUMMARY

Scan ID: 1

7.) SYNCH DATA

SELECT COUNT(*) FROM CMAGNO.TESTE;

COUNT(*)
 ----------
 3

SELECT COUNT(*) FROM CMAGNO.TESTE@LNK_CLONETST;

COUNT(*)
 ----------
 0

DECLARE
 scan_info    DBMS_COMPARISON.COMPARISON_TYPE;
 BEGIN
 DBMS_COMPARISON.CONVERGE( comparison_name  => 'TABLE_TESTE', scan_id => 1 , scan_info => scan_info, 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;
 /

Local Rows Merged: 0
 Remote Rows Merged: 3
 Local Rows Deleted: 0
 Remote Rows Deleted: 0

PL/SQL procedure successfully completed.

>  SELECT COUNT(*) FROM CMAGNO.TESTE@LNK_CLONETST;

COUNT(*)
 ----------
 3