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
