| General |
| Note: DBMS_RECTIFIER_DIFF provides an interface used to
detect and resolve data inconsistencies between two replicated sites. Intended for replicated sites it can
be used within a single database or schema.
|
| Source |
{ORACLE_HOME}/rdbms/admin/dbmsrctf.sql |
| First Available |
8.0 |
| Dependencies |
| DBMS_LOGREP_UTIL |
DBMS_REPUTIL |
| DBMS_RECTIFIER_FRIENDS |
DBMS_SQL |
|
DBMS_REPCAT |
DBMS_SYS_ERROR |
|
DBMS_REPCAT_SQL_UTL |
DBMS_SYS_SQL |
|
DBMS_REPCAT_UTL |
DBMS_UTILITY |
|
| Exceptions |
| Exception Name |
Error Code |
Reason |
| BADNAME |
-23368 |
NULL or empty string for table or schema name |
| BADMRNAME |
-23377 |
|
| BAD_NUMBER |
-23366 |
The commit_rows parameter is less than 1 |
| CANNOTBENULL |
-23369 |
Parameter cannot be NULL |
| DBMS_REPCAT.COMMFAILURE |
-23302 |
Remote site is inaccessible |
| DBMS_REPCAT.MISSINGOBJECT |
-23308 |
Table does not exist |
| DBMS_REPCAT.NOREPOPTION |
|
Replication option is not installed |
| MISSINGPRIMARYKEY |
-23367 |
Column list must include primary key (or SET_COLUMNS equivalent) |
| NOSUCHSITE |
-23365 |
Database site could not be found |
| NOTSHAPEEQUIVALENT |
-23370 |
Tables being compared are not shape equivalent. Shape refers to the number of columns, their column names, and the column datatypes |
| UNKNOWNCOLUMN |
-23371 |
Column does not exist |
| UNSUPPORTEDTYPE |
-23372 |
Data type not supported |
|
| Related Objects |
| ALL_REPRESOLUTION |
DBA_REPRESOL_STATS_CONTROL |
| ALL_REPRESOL_STATS_CONTROL |
USER_REPRESOLUTION |
| ALL_REPRESOLUTION_METHOD |
USER_REPRESOLUTION_METHOD |
| ALL_REPRESOLUTION_STATISTICS |
USER_REPRESOL_STATS_CONTROL |
| DBA_REPRESOLUTION |
USER_REPRESOLUTION_STATISTICS |
| DBA_REPRESOLUTION_METHOD |
RESOURCE_COST |
| DBA_REPRESOLUTION_STATISTICS |
|
|
| Security Model |
Execute on dbms_rectifier_diff is granted to execute_catalog_role |
| |
| DIFFERENCES |
Determines differences between tables. It accepts the storage table of a nested table.
Cannot be used on LOB columns, nor on columns based on user-defined data types.
Overload 1 |
dbms_rectifier_diff.differences (
sname1 VARCHAR2,
oname1 VARCHAR2,
reference_site VARCHAR2 := '',
sname2 VARCHAR2,
oname2 VARCHAR2,
comparison_site VARCHAR2 := '',
where_clause VARCHAR2 := '',
column_list VARCHAR2 := '',
missing_rows_sname VARCHAR2,
missing_rows_oname1 VARCHAR2,
missing_rows_oname2 VARCHAR2,
missing_rows_site VARCHAR2 := '',
max_missing INTEGER,
commit_rows INTEGER := 500); |
-- reference site
table
CREATE TABLE rst (
col1 NUMBER(3),
col2 VARCHAR2(20),
col3 DATE);
INSERT INTO rst (col1, col2, col3) VALUES (1, 'AB', SYSDATE-3);
INSERT INTO rst (col1, col2, col3) VALUES (2, 'CD', SYSDATE-2);
INSERT INTO rst (col1, col2, col3) VALUES (3, 'EF', SYSDATE-1);
INSERT INTO rst (col1, col2, col3) VALUES (4, 'GH', SYSDATE);
INSERT INTO rst (col1, col2, col3) VALUES (5, 'IJ', SYSDATE+1);
-- comparison site table
CREATE TABLE cst AS
SELECT * FROM rst;
SELECT * FROM rst;
SELECT * FROM cst;
UPDATE cst
SET col2 = REVERSE(col2)
WHERE col1 = 2;
UPDATE cst
SET col3 = SYSDATE-20
WHERE col1 = 4;
COMMIT;
SELECT * FROM rst;
SELECT * FROM cst;
-- missing rows table
CREATE TABLE mrt AS
SELECT *
FROM rst
WHERE 1=2;
-- missing rows data
CREATE TABLE mrdata (
r_id ROWID,
present VARCHAR2(100),
absent VARCHAR2(100));
exec dbms_rectifier_diff.differences(sname1 => 'UWCLASS', oname1 => 'RST', reference_site => '',
sname2 => 'UWCLASS', oname2 => 'CST', comparison_site => '', where_clause => NULL, column_list =>
'COL1,COL2,COL3',
missing_rows_sname => 'UWCLASS', missing_rows_oname1 => 'MRT', missing_rows_oname2 => 'MRDATA', missing_rows_site => '',
max_missing => 500, commit_rows => 100);
ALTER TABLE rst
ADD CONSTRAINT pk_rst
PRIMARY KEY (col1);
ALTER TABLE cst
ADD CONSTRAINT pk_cst
PRIMARY KEY (col1);
exec dbms_rectifier_diff.differences(sname1 =>
'UWCLASS', oname1 => 'RST', reference_site => '',
sname2 => 'UWCLASS', oname2 => 'CST', comparison_site => '', where_clause => NULL, column_list =>
'COL1,COL2,COL3',
missing_rows_sname => 'UWCLASS', missing_rows_oname1 => 'MRT', missing_rows_oname2 => 'MRDATA', missing_rows_site => '',
max_missing => 500, commit_rows => 100);
SELECT * FROM rst;
SELECT * FROM cst;
SELECT * FROM mrt;
col linesize 121
col present format a40
col absent format a40
SELECT * FROM mrdata; |
Working code from PSOUG office |
conn / as sysdba
CREATE TABLE psoug.mrt AS
SELECT * FROM psoug.person;
CREATE TABLE psoug.mrdata (
r_id ROWID,
present VARCHAR2(100),
absent VARCHAR2(100));
BEGIN
dbms_rectifier_diff.differences(
sname1 => 'PSOUG',
oname1 => 'PERSON',
reference_site => 'BIGDOG',
sname2 => 'PSOUG',
oname2 => 'PERSON',
comparison_site => '',
where_clause => NULL,
column_list => '',
missing_rows_sname => 'PSOUG',
missing_rows_oname1 => 'MRT',
missing_rows_oname2 => 'MRDATA',
missing_rows_site => '',
max_missing => 4000,
commit_rows => 100);
END;
/ |
Overload 2 |
dbms_rectifier_diff.differences (
sname1 VARCHAR2,
oname1 VARCHAR2,
reference_site VARCHAR2 := '',
sname2 VARCHAR2,
oname2 VARCHAR2,
comparison_site VARCHAR2 := '',
where_clause VARCHAR2 := '',
array_columns dbms_utility.name_array,
missing_rows_sname VARCHAR2,
missing_rows_oname1 VARCHAR2,
missing_rows_oname2 VARCHAR2,
missing_rows_site VARCHAR2 := '',
max_missing INTEGER,
commit_rows INTEGER := 500); |
| TBD |
| |
| RECTIFY |
Resolves the differences between two tables. It
accepts the storage table of a nested table.
Cannot be used on LOB columns, nor on columns based on user-defined data types.
Overload 1 |
dbms_rectifier_diff.rectify (
sname1 VARCHAR2,
oname1 VARCHAR2,
reference_site VARCHAR2 := '',
sname2 VARCHAR2,
oname2 VARCHAR2,
comparison_site VARCHAR2 := '',
column_list VARCHAR2 := '',
missing_rows_sname VARCHAR2,
missing_rows_oname1 VARCHAR2,
missing_rows_oname2 VARCHAR2,
missing_rows_site VARCHAR2 := '',
commit_rows INTEGER := 500); |
exec dbms_rectifier_diff.rectify(sname1 => 'UWCLASS', oname1 => 'RST', reference_site => '',
sname2 => 'UWCLASS', oname2 => 'CST', comparison_site => '', column_list => 'COL1,COL2,COL3', missing_rows_sname => 'UWCLASS',
missing_rows_oname1 => 'MRT', missing_rows_oname2 => 'MRDATA', missing_rows_site => '', commit_rows => 100);
SELECT * FROM rst;
SELECT * FROM cst;
SELECT * FROM mrt;
SELECT * FROM mrdata; |
Overload 2 |
dbms_rectifier_diff.rectify (
sname1 VARCHAR2,
oname1 VARCHAR2,
reference_site VARCHAR2 := '',
sname2 VARCHAR2,
oname2 VARCHAR2,
comparison_site VARCHAR2 := '',
array_columns dbms_utility.name_array,
missing_rows_sname VARCHAR2,
missing_rows_oname1 VARCHAR2,
missing_rows_oname2 VARCHAR2,
missing_rows_site VARCHAR2 := '',
commit_rows INTEGER := 500); |
| TBD |
| |
| TURN_REPLICATION_OFF |
| Turns off replication |
dbms_rectifier_diff.turn_replication_off; |
| exec dbms_rectifier_diff.turn_replication_off; |
| |
| TURN_REPLICATION_ON |
| Turns off replication |
dbms_rectifier_diff.turn_replication_on; |
| exec dbms_rectifier_diff.turn_replication_on; |
|
