Oracle | DBMS_COMPARISON

Oracle DBMS_COMPARISON Version 21c

General Information

Library Note

Morgan's Library Page Header

Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.

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