| General Information |
| Note: Can be used to compare tables, views, and materialized views. |
| Source |
{ORACLE_HOME}/rdbms/admin/dbmscmp.sql |
| First Available |
11.1.0.6 |
| Constants |
| Name |
Data Type |
Value |
| Comparison Mode |
| CMP_COMPARE_MODE_OBJECT |
VARCHAR2(30) |
'OBJECT' |
| Scan Mode |
| CMP_SCAN_MODE_FULL |
VARCHAR2(30) |
'FULL'. |
| CMP_SCAN_MODE_RANDOM |
VARCHAR2(30) |
'RANDOM' |
| CMP_SCAN_MODE_CYCLIC |
VARCHAR2(30) |
'CYCLIC' |
| CMP_SCAN_MODE_CUSTOM |
VARCHAR2(30) |
'CUSTOM' |
| Coverage Option |
| CMP_CONVERGE_LOCAL_WINS |
VARCHAR2(30) |
'LOCAL' |
| CMP_CONVERGE_REMOTE_WINS |
VARCHAR2(30) |
'REMOTE' |
| Null Value |
| NULL_VALUE |
VARCHAR2(100) |
'ORA$STREAMS$NV' |
| Maximum Number of Buckets |
| CMP_MAX_NUM_BUCKETS |
INTEGER |
1000 |
| Minimum Rows in a Bucket |
| CMP_MIN_ROWS_IN_BUCKET |
INTEGER |
10000 |
|
| Defined Data Types |
TYPE comparison_type IS RECORD (
scan_id NUMBER,
loc_rows_merged NUMBER, -- local rows upserted
rmt_rows_merged NUMBER, -- remote rows upserted
loc_rows_deleted NUMBER,
rmt_rows_deleted NUMBER); |
| Dependencies |
| ALL_CONSTRAINTS |
DBMS_CMP_INT |
| ALL_DB_LINKS |
DBMS_LOCK |
| ALL_DEPENDENCIES |
DBMS_LOGREP_UTIL |
| ALL_INDEXES |
DBMS_RANDOM |
| ALL_IND_COLUMNS |
DBMS_REPCAT_DECL |
| ALL_IND_PARTITIONS |
DBMS_SQL |
| ALL_IND_SUBPARTITIONS |
DBMS_STREAMS_ADM |
| ALL_OBJECTS |
DBMS_STREAMS_ADM_UTL |
| ALL_SNAPSHOTS |
DBMS_STREAMS_ADM_UTL_INVOK |
| ALL_SYNONYMS |
DBMS_STREAMS_DECL |
| ALL_TABLES |
DBMS_SYS_ERROR |
| ALL_TAB_COLUMNS |
DBMS_UTILITY |
| COMPARISON$ |
DBMS_XPLAN |
| COMPARISON_ROW_DIF$ |
DBMS_XPLAN_TYPE_TABLE |
| COMPARISON_SCAN_VAL$ |
DUAL |
| DBA_COMPARISON_ROW_DIF |
NLS_SESSION_PARAMETERS |
| DBA_COMPARISON_SCAN |
PLITBLM |
| DBA_COMPARISON_SCAN_SUMMARY |
USER_COMPARISON_COLUMNS |
| DBA_COMPARISON_SCAN_VALUES |
USER_COMPARISON_SCAN |
| DBMS_ASSERT |
_USER_COMPARISON_ROW_DIF |
|
| Security Model |
Execute is granted to EXECUTE_CATALOG_ROLE |
| Subprograms |
|
| |
| COMPARE |
| Perform a comparison identified by comparison name |
dbms_comparison.compare(
comparison_name IN VARCHAR2,
scan_info OUT comparison_type,
min_value IN VARCHAR2 DEFAULT NULL,
max_value IN VARCHAR2 DEFAULT NULL,
perform_row_dif IN BOOLEAN DEFAULT FALSE)
RETURN BOOLEAN; |
set serveroutput on
DECLARE
retval dbms_comparison.comparison_type;
BEGIN
IF dbms_comparison.compare('UWCompare', retval, perform_row_dif=>TRUE) THEN
dbms_output.put_line('No Differences');
ELSE
dbms_output.put_line('Differences Found');
END IF;
END;
/
desc comparison_scan$
SELECT comparison_id, scan_id, num_rows, status, flags
FROM comparison_scan$;
SELECT comparison_id, scan_id, loc_rowid, rmt_rowid, status
FROM comparison_row_dif$;
col last_update_time format a30
col index_value format a11
SELECT *
FROM user_comparison_scan;
SELECT comparison_name, scan_id, local_rowid, index_value, status,
last_update_time
FROM user_comparison_row_dif;
SELECT css.scan_id, uc.comparison_name, uc.schema_name, uc.object_name, css.current_dif_count
FROM user_comparison uc, user_comparison_scan_summary css
WHERE uc.comparison_name = css.comparison_name
AND css.scan_id = 2;
col index_value format a20
SELECT ucc.column_name, ucrd.index_value,
CASE WHEN ucrd.local_rowid IS NULL THEN 'No'
ELSE 'Yes'
END AS LOCAL_ROWID,
CASE WHEN ucrd.REMOTE_ROWID IS NULL THEN 'No'
ELSE 'Yes'
END AS REMOTE_ROWID
FROM user_comparison_columns ucc, user_comparison_row_dif ucrd, user_comparison_scan ucs
WHERE ucrd.scan_id = ucs.scan_id
AND ucc.comparison_name = ucrd.comparison_name
AND ucc.comparison_name = 'UWCOMPARE'
AND ucrd.status = 'DIF'
AND ucc.INDEX_COLUMN = 'Y'
ORDER BY 2;
-- next run converge procedure |
| |
| CONVERGE |
| Execute compensating DML to get the two objects to converge |
dbms_comparison.converge(
comparison_name IN VARCHAR2,
scan_id IN NUMBER,
scan_info OUT comparison_type,
converge_options IN VARCHAR2 DEFAULT CMP_CONVERGE_LOCAL_WINS,
perform_commit IN BOOLEAN DEFAULT TRUE,
local_converge_tag IN RAW DEFAULT NULL,
remote_converge_tag IN RAW DEFAULT NULL); |
SELECT * FROM scott.dept
MINUS
SELECT * FROM abc.dept;
SELECT * FROM abc.emp
MINUS
SELECT * FROM scott.emp;
set serveroutput on
DECLARE
ct dbms_comparison.comparison_type;
BEGIN
dbms_comparison.converge('UWCOMPARE', 2, ct, dbms_comparison.CMP_CONVERGE_LOCAL_WINS, TRUE);
dbms_output.put_line(ct.scan_id);
dbms_output.put_line(ct.loc_rows_merged);
dbms_output.put_line(ct.rmt_rows_merged);
dbms_output.put_line(ct.loc_rows_deleted);
dbms_output.put_line(ct.rmt_rows_merged);
END;
/
SELECT * FROM scott.dept
MINUS
SELECT * FROM abc.dept;
SELECT * FROM abc.emp
MINUS
SELECT * FROM scott.emp; |
| |
| CREATE_COMPARISON |
| Creates a comparison |
dbms_comparison.create_comparison(
comparison_name IN VARCHAR2, -- can not contain spaces
schema_name IN VARCHAR2,
object_name IN VARCHAR2,
dblink_name IN VARCHAR2,
index_schema_name IN VARCHAR2 DEFAULT NULL,
index_name IN VARCHAR2 DEFAULT NULL,
remote_schema_name IN VARCHAR2 DEFAULT NULL,
remote_object_name IN VARCHAR2 DEFAULT NULL,
comparison_mode IN VARCHAR2 DEFAULT CMP_COMPARE_MODE_OBJECT,
column_list IN VARCHAR2 DEFAULT '*',
scan_mode IN VARCHAR2 DEFAULT CMP_SCAN_MODE_FULL,
scan_percent IN NUMBER DEFAULT NULL,
null_value IN VARCHAR2 DEFAULT CMP_NULL_VALUE_DEF,
local_converge_tag IN RAW DEFAULT NULL,
remote_converge_tag IN RAW DEFAULT NULL,
max_num_buckets IN NUMBER DEFAULT CMP_MAX_NUM_BUCKETS,
min_rows_in_bucket IN NUMBER DEFAULT CMP_MIN_ROWS_IN_BUCKET); |
conn / as sysdba
create user abc
identified by abc
default tablespace uwdata
temporary tablespace temp
quota unlimited on uwdata;
GRANT create session TO abc;
GRANT create table TO abc;
conn scott/tiger
GRANT select ON emp TO abc;
GRANT select ON dept TO abc;
conn abc/abc
CREATE TABLE dept AS
SELECT * FROM scott.dept;
DELETE FROM dept WHERE rownum = 1;
CREATE TABLE emp AS
SELECT * FROM scott.emp;
DELETE FROM emp WHERE deptno = 10;
COMMIT;
conn / as sysdba
exec dbms_comparison.create_comparison (comparison_name=>'UWCompare', schema_name=>'SCOTT',
object_name=>'DEPT', dblink_name=>NULL, remote_schema_name=>'ABC', remote_object_name=>'DEPT', scan_percent=>90);
set linesize 121
col comparison_name format a15
col schema_name format a10
col object_name format a10
col rmt_schema_name format a15
col rmt_object_name format a10
SELECT comparison_name, comparison_mode, schema_name, object_name,
rmt_schema_name, rmt_object_name, scan_percent
FROM comparison$;
desc user_comparison_columns
SELECT comparison_name, column_position, column_name, index_column
FROM user_comparison_columns;
-- next run the compare function |
| |
| DROP_COMPARISON |
| Drop a comparison |
dbms_comparison.drop_comparison(comparison_name IN VARCHAR2); |
| exec dbms_comparison.drop_comparison('UWCompare'); |
| |
| PURGE_COMPARISON |
| Purge a comparison's results or a subset of it |
dbms_comparison.purge_comparison(
comparison_name IN VARCHAR2,
scan_id IN NUMBER DEFAULT NULL,
purge_time IN TIMESTAMP DEFAULT NULL); |
SELECT comparison_id, scan_id, num_rows, status, flags, spare4
FROM comparison_scan$;
exec dbms_comparison.purge_comparison('UWCompare');
SELECT comparison_id, scan_id, num_rows, status, flags, spare4
FROM comparison_scan$; |
| |
| RECHECK |
| Recheck a specified scan |
dbms_comparison. recheck(
comparison_name IN VARCHAR2,
scan_id IN NUMBER,
perform_row_dif IN BOOLEAN DEFAULT FALSE)
RETURN BOOLEAN; |
desc sys.comparison_scan$
SELECT comparison_id, scan_id, num_rows, status, flags
FROM comparison_scan$;
set serveroutput on
DECLARE
retval dbms_comparison.comparison_type;
BEGIN
IF dbms_comparison.recheck('UWCompare', 2, perform_row_dif=>TRUE) THEN
dbms_output.put_line('No Differences');
ELSE
dbms_output.put_line('Differences Found');
END IF;
END;
/
SELECT comparison_id, scan_id, num_rows, status, flags
FROM comparison_scan$; |
|
