Nota: los valores para el campo SCAN_ID, deben ser actualizados con los valores que ustedes obtienen de la consultas a la hora de creación del conjunto de comparación.
SQL> connect system/oracle@pdb1
Connected.
SQL> select BANNER_FULL from v$version;
BANNER_FULL
------------------------------------------------------------------
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
SQL>
SQL> desc DBMS_COMPARISON
FUNCTION COMPARE RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMPARISON_NAME VARCHAR2 IN
SCAN_INFO COMPARISON_TYPE OUT
MIN_VALUE VARCHAR2 IN DEFAULT
MAX_VALUE VARCHAR2 IN DEFAULT
PERFORM_ROW_DIF BOOLEAN IN DEFAULT
PROCEDURE CONVERGE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMPARISON_NAME VARCHAR2 IN
SCAN_ID NUMBER IN
SCAN_INFO COMPARISON_TYPE OUT
CONVERGE_OPTIONS VARCHAR2 IN DEFAULT
PERFORM_COMMIT BOOLEAN IN DEFAULT
LOCAL_CONVERGE_TAG RAW IN DEFAULT
REMOTE_CONVERGE_TAG RAW IN DEFAULT
PROCEDURE CREATE_COMPARISON
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMPARISON_NAME VARCHAR2 IN
SCHEMA_NAME VARCHAR2 IN
OBJECT_NAME VARCHAR2 IN
DBLINK_NAME VARCHAR2 IN
INDEX_SCHEMA_NAME VARCHAR2 IN DEFAULT
INDEX_NAME VARCHAR2 IN DEFAULT
REMOTE_SCHEMA_NAME VARCHAR2 IN DEFAULT
REMOTE_OBJECT_NAME VARCHAR2 IN DEFAULT
COMPARISON_MODE VARCHAR2 IN DEFAULT
COLUMN_LIST VARCHAR2 IN DEFAULT
SCAN_MODE VARCHAR2 IN DEFAULT
SCAN_PERCENT NUMBER IN DEFAULT
NULL_VALUE VARCHAR2 IN DEFAULT
LOCAL_CONVERGE_TAG RAW IN DEFAULT
REMOTE_CONVERGE_TAG RAW IN DEFAULT
MAX_NUM_BUCKETS NUMBER IN DEFAULT
MIN_ROWS_IN_BUCKET NUMBER IN DEFAULT
PROCEDURE DROP_COMPARISON
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMPARISON_NAME VARCHAR2 IN
PROCEDURE PURGE_COMPARISON
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMPARISON_NAME VARCHAR2 IN
SCAN_ID NUMBER IN DEFAULT
PURGE_TIME TIMESTAMP IN DEFAULT
FUNCTION RECHECK RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMPARISON_NAME VARCHAR2 IN
SCAN_ID NUMBER IN
PERFORM_ROW_DIF BOOLEAN IN DEFAULT
SQL> connect sys/oracle@pdb1 as sysdba
Connected.
SQL> grant execute on DBMS_COMPARISON to hr;
Grant succeeded.
SQL> grant execute_catalog_role to hr;
Grant succeeded.
SQL> connect hr/oracle@pdb1
Connected.
SQL> select * from cat;
TABLE_NAME TABLE_TYPE
------------------------------ -----------
REGIONS TABLE
COUNTRIES TABLE
LOCATIONS TABLE
LOCATIONS_SEQ SEQUENCE
DEPARTMENTS TABLE
DEPARTMENTS_SEQ SEQUENCE
JOBS TABLE
EMPLOYEES TABLE
EMPLOYEES_SEQ SEQUENCE
JOB_HISTORY TABLE
EMP_DETAILS_VIEW VIEW
11 rows selected.
SQL> create table employees_copy as select * from employees;
Table created.
SQL> desc employees_copy
Name Null? Type
------------------------------------ -------- ---------------
EMPLOYEE_ID NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
SQL> select distinct department_id from employees_copy;
DEPARTMENT_ID
-------------
90
60
100
30
50
80
10
20
40
70
110
12 rows selected.
SQL> delete employees_copy where department_id=90;
3 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from employees_copy;
COUNT(*)
----------
104
SQL> select count(*) from employees;
COUNT(*)
----------
107
SQL> BEGIN
DBMS_COMPARISON.CREATE_COMPARISON
( comparison_name => 'comparar_tablas_empleados'
, schema_name => 'hr'
, object_name => 'employees'
, dblink_name => null
, remote_schema_name=>'hr'
, remote_object_name=>'employees_copy'
);
END;
/
BEGIN
*
ERROR at line 1:
ORA-23676: no eligible index on local table "HR"."EMPLOYEES_COPY"
ORA-06512: at "SYS.DBMS_COMPARISON", line 5240
ORA-06512: at "SYS.DBMS_COMPARISON", line 1989
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 140
ORA-06512: at "SYS.DBMS_COMPARISON", line 1942
ORA-06512: at "SYS.DBMS_COMPARISON", line 5189
ORA-06512: at "SYS.DBMS_COMPARISON", line 455
ORA-06512: at line 2
SQL> desc EMPLOYEES_COPY
Name Null? Type
------------------------------------------- -------- --------------
EMPLOYEE_ID NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
SQL> alter table EMPLOYEES_COPY add constraint PK_EMPLOYEES_COPY PRIMARY KEY(EMPLOYEE_ID);
Table altered.
SQL> BEGIN
DBMS_COMPARISON.CREATE_COMPARISON
( comparison_name => 'comparar_tablas_empleados'
, schema_name => 'hr'
, object_name => 'employees'
, dblink_name => null
, remote_schema_name=>'hr'
, remote_object_name=>'employees_copy'
);
END;
/
PL/SQL procedure successfully completed.
SQL> DECLARE
consistent BOOLEAN;
scan_info DBMS_COMPARISON.COMPARISON_TYPE;
BEGIN
consistent := DBMS_COMPARISON.COMPARE
( comparison_name => 'comparar_tablas_empleados'
, 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 se encuentran diferencias.');
ELSE
DBMS_OUTPUT.PUT_LINE('Diferencias encontradas.');
END IF;
END;
/
Scan ID: 10
Diferencias encontradas.
SQL> 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 = 43
/
SCAN_ID COMPARISON_NAME SCHEMA_NAM OBJECT_NAME CURRENT_DIF_COUNT
---------- ------------------------------ ---------- ------------------------------ -----------------
10 COMPARAR_TABLAS_EMPLEADOS HR EMPLOYEES 3
SQL> select * from USER_COMPARISON_ROW_DIF
where scan_id=43;
COMPARISON_NAME SCAN_ID LOCAL_ROWID REMOTE_ROWID INDEX_VALU STA LAST_UPDATE_TIME
------------------------------ ---------- ------------------ ------------------ ---------- --- --------------------
COMPARAR_TABLAS_EMPLEADOS 11 AAATA2AAPAAAADOAAA 100 DIF 25-APR-23 06.26.47.3
83334 PM
COMPARAR_TABLAS_EMPLEADOS 11 AAATA2AAPAAAADOAAB 101 DIF 25-APR-23 06.26.47.3
83334 PM
COMPARAR_TABLAS_EMPLEADOS 11 AAATA2AAPAAAADOAAC 102 DIF 25-APR-23 06.26.47.3
83334 PM
SQL> COL COLUMN_NAME FORMAT A20
SQL> SELECT c.COLUMN_NAME
, r.INDEX_VALUE
, case
when r.LOCAL_ROWID is null
then 'No'
else 'Yes'
end LOCAL_ROWID
, case
when r.REMOTE_ROWID is null
then 'No'
else 'Yes'
end REMOTE_ROWID
FROM USER_COMPARISON_COLUMNS c
, USER_COMPARISON_ROW_DIF r
, USER_COMPARISON_SCAN s
WHERE c.COMPARISON_NAME = 'COMPARA_TABLAS_T'
AND r.SCAN_ID = s.SCAN_ID and s.SCAN_ID=47
AND r.STATUS like '%DIF%'
AND c.INDEX_COLUMN = 'Y'
AND c.COMPARISON_NAME = r.COMPARISON_NAME
ORDER BY r.INDEX_VALUE
/
COLUMN_NAME INDEX_VALU LOC REM
-------------------- ---------- --- ---
EMPLOYEE_ID 100 No Yes
EMPLOYEE_ID 101 No Yes
EMPLOYEE_ID 102 No Yes
SQL>
SQL> DECLARE
scan_info DBMS_COMPARISON.COMPARISON_TYPE;
BEGIN
DBMS_COMPARISON.CONVERGE
( comparison_name => 'COMPARAR_TABLAS_EMPLEADOS'
, scan_id => 42
, 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.
SQL>
SQL> select count(*) from employees;
COUNT(*)
----------
107
SQL> select count(*) from employees_copy;
COUNT(*)
----------
107
SQL> execute DBMS_COMPARISON.DROP_COMPARISON('COMPARAR_TABLAS_EMPLEADOS');
SQL> show user
USER is "HR"
SQL> create table t1 ( x number, y char);
Table created.
SQL> create table t2 ( x number, y char);
create table t3 ( x number, y char);
Table created.
SQL> begin
for i in 1 ..10 loop
insert into t1 values ( i, 'X');
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> begin
for i in 1 ..5 loop
insert into t2 values ( i, 'X');
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select count(*) from t2;
COUNT(*)
----------
5
SQL> select count(*) from t1;
COUNT(*)
----------
10
SQL> select * from t1;
X Y
---------- -
1 X
2 X
3 X
4 X
5 X
6 X
7 X
8 X
9 X
10 X
10 rows selected.
SQL> select * from t2;
X Y
---------- -
1 X
2 X
3 X
4 X
5 X
SQL> alter table t1 add constraint PK_T1 primary key(x);
Table altered.
SQL> alter table t2 add constraint PK_T2 primary key(x);
Table altered.
SQL> BEGIN
DBMS_COMPARISON.CREATE_COMPARISON
( comparison_name => 'COMPARA_TABLAS_T'
, schema_name => 'hr'
, object_name => 't2'
, dblink_name => null
, remote_schema_name=>'hr'
, remote_object_name=>'t1'
);
END;
/
PL/SQL procedure successfully completed.
SQL> DECLARE
consistent BOOLEAN;
scan_info DBMS_COMPARISON.COMPARISON_TYPE;
BEGIN
consistent := DBMS_COMPARISON.COMPARE
( comparison_name => 'COMPARA_TABLAS_T'
, 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 se encuentran diferencias.');
ELSE
DBMS_OUTPUT.PUT_LINE('Diferencias encontradas.');
END IF;
END;
/
Scan ID: 4
Diferencias encontradas.
PL/SQL procedure successfully completed.
SQL> 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 = 1
/
no rows selected
SQL> 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 = 47
/
SCAN_ID COMPARISON_NAME SCHEMA_NAM OBJECT_NAME CURRENT_DIF_COUNT
---------- ------------------------------ ---------- --------------- -----------------
4 COMPARA_TABLAS_T HR T2 5
SQL> DECLARE
scan_info DBMS_COMPARISON.COMPARISON_TYPE;
BEGIN
DBMS_COMPARISON.CONVERGE
( comparison_name => 'COMPARA_TABLAS_T'
, scan_id => 45
, 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: 0
Local Rows Deleted: 0
Remote Rows Deleted: 5
PL/SQL procedure successfully completed.
SQL> select count(*) from t1;
COUNT(*)
----------
5
SQL> select count(*) from t2;
COUNT(*)
----------
5
SQL> truncate table t1;
Table truncated.
SQL> truncate table t2;
Table truncated.
SQL> begin
for i in 1 ..10 loop
insert into t1 values ( i, 'X');
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> begin
for i in 1 ..5 loop
insert into t2 values ( i, 'X');
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select count(*) from t1;
COUNT(*)
----------
10
SQL> select count(*) from t2;
COUNT(*)
----------
5
SQL> execute DBMS_COMPARISON.DROP_COMPARISON('COMPARA_TABLAS_T');
PL/SQL procedure successfully completed.
SQL> BEGIN
DBMS_COMPARISON.CREATE_COMPARISON
( comparison_name => 'COMPARA_TABLAS_T'
, schema_name => 'hr'
, object_name => 't1'
, dblink_name => null
, remote_schema_name=>'hr'
, remote_object_name=>'t2'
);
END;
/
PL/SQL procedure successfully completed.
SQL> DECLARE
consistent BOOLEAN;
scan_info DBMS_COMPARISON.COMPARISON_TYPE;
BEGIN
consistent := DBMS_COMPARISON.COMPARE
( comparison_name => 'COMPARA_TABLAS_T'
, 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 se encuentran diferencias.');
ELSE
DBMS_OUTPUT.PUT_LINE('Diferencias encontradas.');
END IF;
END;
/
Scan ID: 6
Diferencias encontradas.
PL/SQL procedure successfully completed.
SQL> 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 = 6
/
SCAN_ID COMPARISON_NAME SCHEMA_NAM OBJECT_NAME CURRENT_DIF_COUNT
---------- ---------------------- ---------- --------------- -----------------
6 COMPARA_TABLAS_T HR T1 5
SQL> DECLARE
scan_info DBMS_COMPARISON.COMPARISON_TYPE;
BEGIN
DBMS_COMPARISON.CONVERGE
( comparison_name => 'COMPARA_TABLAS_T'
, scan_id => 47
, 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: 5
Local Rows Deleted: 0
Remote Rows Deleted: 0
PL/SQL procedure successfully completed.
SQL> select count(*) from t1;
COUNT(*)
----------
10
SQL> select count(*) from t2;
COUNT(*)
----------
10
SQL>
**********************************
SQL> truncate table t3;
Table truncated.
SQL> truncate table t1;
Table truncated.
SQL> begin
for i in 1 ..10 loop
insert into t1 values ( i, 'X');
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> begin
for i in 1 ..5 loop
insert into t2 values ( i, 'Y');
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> BEGIN
DBMS_COMPARISON.CREATE_COMPARISON
( comparison_name => 'COMPARA_TABLAS_T'
, schema_name => 'hr'
, object_name => 't1'
, dblink_name => null
, remote_schema_name=>'hr'
, remote_object_name=>'t2'
, column_list =>'X'
);
END;
/ 3 4 5 6 7 8 9 10 11 12
PL/SQL procedure successfully completed.
SQL> set serveroutput on
SQL> DECLARE
consistent BOOLEAN;
scan_info DBMS_COMPARISON.COMPARISON_TYPE;
BEGIN
consistent := DBMS_COMPARISON.COMPARE
( comparison_name => 'COMPARA_TABLAS_T'
, 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 se encuentran diferencias.');
ELSE
DBMS_OUTPUT.PUT_LINE('Diferencias encontradas.');
END IF;
END;
/
Scan ID: 24
Diferencias encontradas.
PL/SQL procedure successfully completed.
SQL> col COMPARISON_NAME format a20
SQL> col SCHEMA_NAME format a10
SQL> col OBJECT_NAME format a10
SQL> 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 = 53
/
SCAN_ID COMPARISON_NAME SCHEMA_NAM OBJECT_NAM CURRENT_DIF_COUNT
---------- -------------------- ---------- ---------- -----------------
24 COMPARA_TABLAS_T HR T1 10
SQL> DECLARE
scan_info DBMS_COMPARISON.COMPARISON_TYPE;
BEGIN
DBMS_COMPARISON.CONVERGE
( comparison_name => 'COMPARA_TABLAS_T'
, scan_id => 53
, 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: 10
Local Rows Deleted: 0
Remote Rows Deleted: 0
PL/SQL procedure successfully completed.
SQL> select * from t2;
X Y
---------- -
1 X
2 X
3 X
4 X
5 X
6 X
7 X
8 X
9 X
10 X
10 rows selected.
SQL> select * from t1;
X Y
---------- -
1 X
2 X
3 X
4 X
5 X
6 X
7 X
8 X
9 X
10 X
10 rows selected.
SQL>
****************************
SQL> truncate table t1;
Table truncated.
SQL> truncate table t2;
Table truncated.
SQL> begin
for i in 1 ..10 loop
insert into t1 values ( i, 'X');
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> begin
for i in 1 ..5 loop
insert into t2 values ( i, 'Y');
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> BEGIN
DBMS_COMPARISON.CREATE_COMPARISON
( comparison_name => 'COMPARA_TABLAS_T'
, schema_name => 'hr'
, object_name => 't1'
, dblink_name => null
, remote_schema_name=>'hr'
, remote_object_name=>'t2'
);
END;
/
BEGIN
*
ERROR at line 1:
ORA-23627: Comparison object "HR"."COMPARA_TABLAS_T" already existed.
ORA-06512: at "SYS.DBMS_COMPARISON", line 5240
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_CMP_INT", line 277
ORA-06512: at "SYS.DBMS_COMPARISON", line 5222
ORA-06512: at "SYS.DBMS_COMPARISON", line 455
ORA-06512: at line 2
SQL> execute DBMS_COMPARISON.DROP_COMPARISON('COMPARA_TABLAS_T');
PL/SQL procedure successfully completed.
SQL> BEGIN
DBMS_COMPARISON.CREATE_COMPARISON
( comparison_name => 'COMPARA_TABLAS_T'
, schema_name => 'hr'
, object_name => 't1'
, dblink_name => null
, remote_schema_name=>'hr'
, remote_object_name=>'t2'
);
END;
/
PL/SQL procedure successfully completed.
SQL> DECLARE
consistent BOOLEAN;
scan_info DBMS_COMPARISON.COMPARISON_TYPE;
BEGIN
consistent := DBMS_COMPARISON.COMPARE
( comparison_name => 'COMPARA_TABLAS_T'
, 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 se encuentran diferencias.');
ELSE
DBMS_OUTPUT.PUT_LINE('Diferencias encontradas.');
END IF;
END;
/
Scan ID: 27
Diferencias encontradas.
PL/SQL procedure successfully completed.
SQL> 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 = 27
/
SCAN_ID COMPARISON_NAME SCHEMA_NAM OBJECT_NAM CURRENT_DIF_COUNT
---------- -------------------- ---------- ---------- -----------------
27 COMPARA_TABLAS_T HR T1 10
SQL> execute DBMS_COMPARISON.DROP_COMPARISON('COMPARA_TABLAS_T');
PL/SQL procedure successfully completed.
SQL> desc t1
Name Null? Type
------------------------- -------- ---------
X NOT NULL NUMBER
Y CHAR(1)
SQL> BEGIN
DBMS_COMPARISON.CREATE_COMPARISON
( comparison_name => 'COMPARA_TABLAS_T'
, schema_name => 'hr'
, object_name => 't1'
, dblink_name => null
, remote_schema_name=>'hr'
, remote_object_name=>'t2'
, column_list =>'X'
);
END;
/
PL/SQL procedure successfully completed.
SQL> 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 = 27
/
no rows selected
SQL> select * from t1;
X Y
---------- -
1 X
2 X
3 X
4 X
5 X
6 X
7 X
8 X
9 X
10 X
10 rows selected.
SQL> select * from t2;
X Y
---------- -
1 Y
2 Y
3 Y
4 Y
5 Y
SQL> truncate table t2;
Table truncated.
SQL> begin
for i in 11 ..15 loop
insert into t2 values ( i, 'Y');
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> execute DBMS_COMPARISON.DROP_COMPARISON('COMPARA_TABLAS_T');
PL/SQL procedure successfully completed.
SQL> BEGIN
DBMS_COMPARISON.CREATE_COMPARISON
( comparison_name => 'COMPARA_TABLAS_T'
, schema_name => 'hr'
, object_name => 't1'
, dblink_name => null
, remote_schema_name=>'hr'
, remote_object_name=>'t2'
, column_list =>'X'
);
END;
/
PL/SQL procedure successfully completed.
SQL> DECLARE
consistent BOOLEAN;
scan_info DBMS_COMPARISON.COMPARISON_TYPE;
BEGIN
consistent := DBMS_COMPARISON.COMPARE
( comparison_name => 'COMPARA_TABLAS_T'
, 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 se encuentran diferencias.');
ELSE
DBMS_OUTPUT.PUT_LINE('Diferencias encontradas.');
END IF;
END;
/
Scan ID: 30
Diferencias encontradas.
PL/SQL procedure successfully completed.
SQL> 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 = 30
/
SCAN_ID COMPARISON_NAME SCHEMA_NAM OBJECT_NAM CURRENT_DIF_COUNT
---------- -------------------- ---------- ---------- -----------------
30 COMPARA_TABLAS_T HR T1 15
SQL>
SQL> DECLARE
scan_info DBMS_COMPARISON.COMPARISON_TYPE;
BEGIN
DBMS_COMPARISON.CONVERGE
( comparison_name => 'COMPARA_TABLAS_T'
, scan_id => 30
, 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: 10
Local Rows Deleted: 0
Remote Rows Deleted: 5
PL/SQL procedure successfully completed.
SQL> select * from t1;
X Y
---------- -
1 X
2 X
3 X
4 X
5 X
6 X
7 X
8 X
9 X
10 X
10 rows selected.
SQL> select * from t2;
X Y
---------- -
1
2
3
4
5
6
7
8
9
10
10 rows selected.
SQL>
No hay comentarios:
Publicar un comentario
Te agradezco tus comentarios. Te esperamos de vuelta.