CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_COMPARISON

Version 11.1
 
General Information
Note: Can be used to compare tables, views, and materialized views backward compatible to 10gR1 due to need for ORA_HASH.
Source {ORACLE_HOME}/rdbms/admin/dbmscmp.sql
First Available 11.1

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
comparison$ comparison_row_dif$
all_constraints dbms_lock
all_db_links dbms_logrep_util
all_dependencies dbms_random
all_indexes dbms_repcat_decl
all_ind_columns dbms_sql
all_objects dbms_streams
all_snapshots dbms_streams_adm_utl
all_synonyms dbms_streams_decl
all_tables dbms_sys_error
all_tab_cols dbms_utility
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_row_dif
dbms_assert user_comparison_scan
dbms_cmp_int  
Security Model Execute is granted to EXECUTE_CATALOG_ROLE
 
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 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
Streams
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [235 users online]    © 2010 psoug.org