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