Oracle | DBMS_COMPARISON

Oracle DBMS_COMPARISON Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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_STANDARD
ALL_DB_LINKS	COMPARISON_SCAN$	DBMS_STREAMS_ADM
ALL_DEPENDENCIES	COMPARISON_SCAN_VAL$	DBMS_STREAMS_ADM_UTL
ALL_INDEXES	DBA_COMPARISON_COLUMNS	DBMS_STREAMS_ADM_UTL_INVOK
ALL_IND_COLUMNS	DBA_COMPARISON_SCAN	DBMS_STREAMS_DECL
ALL_IND_PARTITIONS	DBA_COMPARISON_SCAN_SUMMARY	DBMS_SYS_ERROR
ALL_IND_SUBPARTITIONS	DBMS_ASSERT	DBMS_UTILITY
ALL_OBJECTS	DBMS_CMP_INT	DBMS_XPLAN
ALL_SNAPSHOTS	DBMS_LOCK	DUAL
ALL_SYNONYMS	DBMS_LOGREP_UTIL	NLS_SESSION_PARAMETERS
ALL_TABLES	DBMS_PRIV_CAPTURE	PLITBLM
ALL_TAB_COLUMNS	DBMS_RANDOM	USER_COMPARISON_SCAN
COMPARISON$	DBMS_SQL	_USER_COMPARISON_ROW_DIF

Documented Yes: Packages and Types Reference

First Available 11.1

Security Model Owned by SYS with with EXECUTE granted to the EXECUTE_CATALOG_ROLE role

Source {ORACLE_HOME}/rdbms/admin/dbmscmp.sql

Subprograms

COMPARE	CREATE_COMPARISON	PURGE_COMPARISON
CONVERGE	DROP_COMPARISON	RECHECK

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