Oracle DBMS_COMPARISON
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
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
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 DBMS_ASSERT DBMS_UTILITY
ALL_IND_SUBPARTITIONS DBMS_CMP_INT DBMS_XPLAN
ALL_OBJECTS DBMS_LOCK DUAL
ALL_SNAPSHOTS DBMS_LOGREP_UTIL NLS_SESSION_PARAMETERS
ALL_SYNONYMS DBMS_RANDOM PLITBLM
ALL_TABLES DBMS_REPCAT_DECL USER_COMPARISON_SCAN
ALL_TAB_COLUMNS DBMS_SQL _USER_COMPARISON_ROW_DIF
COMPARISON$    
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, -- 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 sys@pdborcl 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@pdborcl

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

conn abc/abc@pdborcl

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@pdborcl 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@pdborcl 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
Data Guard - Logical
DBMS_RECTIFIER_DIFF
Materialized Views
Packages
Streams

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-2014 Daniel A. Morgan All Rights Reserved