| General Information |
| 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_ASSERT |
DBMS_REPCAT_SQL_UTL |
DBMS_SYS_ERROR |
| DBMS_LOGREP_UTIL |
DBMS_REPCAT_UTL |
DBMS_SYS_SQL |
| DBMS_RECTIFIER_FRIENDS |
DBMS_REPUTIL |
DBMS_UTILITY |
| DBMS_REPCAT |
DBMS_SQL |
|
|
| Exceptions |
| Exception Name |
Error Number |
Reason |
| BADNAME |
ORA-23368 |
NULL or empty string for table or schema name |
| BADMRNAME |
ORA-23377 |
|
| BAD_NUMBER |
ORA-23366 |
The commit_rows parameter is less than 1 |
| CANNOTBENULL |
ORA-23369 |
Parameter cannot be NULL |
| MISSINGPRIMARYKEY |
ORA-23367 |
Column list must include primary key (or SET_COLUMNS equivalent) |
| NOSUCHSITE |
ORA-23365 |
Database site could not be found |
| NOTSHAPEEQUIVALENT |
ORA-23370 |
Tables being compared are not shape equivalent. Shape refers to the number of columns, their column names, and the column datatypes |
| UNKNOWNCOLUMN |
ORA-23371 |
Column does not exist |
| UNSUPPORTEDTYPE |
ORA-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. The package is now created with AUTHID CURRENT_USER |
| Subprograms |
|
| |
| 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 the Morgan's Library office |
conn / as sysdba
CREATE TABLE mlib.mrt AS
SELECT * FROM mlib.person;
CREATE TABLE mlib.mrdata (
r_id ROWID,
present VARCHAR2(100),
absent VARCHAR2(100));
BEGIN
dbms_rectifier_diff.differences(
sname1 => 'MLIB',
oname1 => 'PERSON',
reference_site => 'BIGDOG',
sname2 => 'MLIB',
oname2 => 'PERSON',
comparison_site => '',
where_clause => NULL,
column_list => '',
missing_rows_sname => 'MLIB',
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; |
|
