Oracle DBMS_COMPARISON
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
Purpose Can be used to compare tables, views, and materialized views.
AUTHID CURRENT_USER
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
CMinimum Rows in a Bucket
CMP_MIN_ROWS_IN_BUCKET INTEGER 10000
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 (Updated to 12.2.0.1)
ALL_CONSTRAINTS COMPARISON_ROW_DIF$ DBMS_STREAMS_ADM
ALL_DB_LINKS COMPARISON_SCAN$ DBMS_STREAMS_ADM_UTL
ALL_DEPENDENCIES COMPARISON_SCAN_VAL$ DBMS_STREAMS_ADM_UTL_INVOK
ALL_INDEXES DBA_COMPARISON_COLUMNS DBMS_STREAMS_DECL
ALL_IND_COLUMNS DBA_COMPARISON_SCAN DBMS_SYS_ERROR
ALL_IND_PARTITIONS DBA_COMPARISON_SCAN_SUMMARY DBMS_UTILITY
ALL_IND_SUBPARTITIONS DBMS_ASSERT DBMS_XPLAN
ALL_OBJECTS DBMS_CMP_INT DUAL
ALL_SNAPSHOTS DBMS_LOCK NLS_SESSION_PARAMETERS
ALL_SYNONYMS DBMS_LOGREP_UTIL PLITBLM
ALL_TABLES DBMS_RANDOM USER_COMPARISON_SCAN
ALL_TAB_COLUMNS DBMS_SQL _USER_COMPARISON_ROW_DIF
COMPARISON$ DBMS_STANDARD  
Documented Yes
First Available 11.1.0.6
Security Model Owned by SYS with with EXECUTE granted to the EXECUTE_CATALOG_ROLE role
Source {ORACLE_HOME}/rdbms/admin/dbmscmp.sql
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;
conn sys@pdbdev as sysdba

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);
conn sys@pdbdev as sysdba

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 new comparison dbms_comparison.create_comparison(
comparison_name     IN VARCHAR2, -- cannot 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 sys@pdbdev as sysdba

CREATE USER abc
IDENTIFIED BY abc
DEFAULT tablespace example
TEMPORARY tablespace temp
QUOTA UNLIMITED ON example;

GRANT create session TO abc;
GRANT create table TO abc;

conn scott/tiger@pdbdev

GRANT select ON emp TO abc;
GRANT select ON dept TO abc;

conn abc/abc@pdbdev

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 sys@pdbdev 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);
conn sys@pdbdev as sysdba

exec dbms_comparison.drop_comparison('UWCompare');
 
PURGE_COMPARISON
Purge a comparison's results or a subset of the results created in a previous run dbms_comparison.purge_comparison(
comparison_name IN VARCHAR2,
scan_id         IN NUMBER    DEFAULT NULL,
purge_time      IN TIMESTAMP DEFAULT NULL);
conn sys@pdbdev as sysdba

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;
conn sys@pdbdev as sysdba

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$;

Related Topics
Built-in Functions
Built-in Packages
Data Guard - Logical
Materialized Views
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved