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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s