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