Oracle DBMS_RECTIFIER_DIFF
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose 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.
AUTHID CURRENT_USER
Dependencies
DBMS_RECTIFIER_DIFF_INTERNAL DBMS_REPCAT_ADMIN DBMS_UTILITY
DBMS_RECTIFIER_FRIENDS DBMS_STANDARD  
Documented Yes
Exceptions
Error Code Reason
ORA-23365 NOSUCHSITE: Database site could not be found
ORA-23366 BAD_NUMBER: The commit_rows parameter is less than 1
ORA-23367 MISSINGPRIMARYKEY: Column list must include primary key (or SET_COLUMNS equivalent)
ORA-23368 BADNAME: NULL or empty string for table or schema name
ORA-23369 CANNOTBENULL: Parameter cannot be NULL
ORA-23370 NOTSHAPEEQUIVALENT: Tables being compared are not shape equivalent. Shape refers to the number of columns, their column names, and the column datatypes
ORA-23371 UNKNOWNCOLUMN: Column does not exist
ORA-23372 UNSUPPORTEDTYPE: Data type not supported
ORA-23377 BADMRNAME
First Available 8.0
Security Model Owned by SYS with EXECUTE granted to the EXECUTE_CATALOG_ROLE role
Source {ORACLE_HOME}/rdbms/admin/dbmsrctf.sql
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);
conn uwclass/uwclass@pdbdev

-- 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;
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);
conn sys@pdbdev as sysdba

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
Stop rectification dbms_rectifier_diff.turn_replication_off;
exec dbms_rectifier_diff.turn_replication_off;
 
TURN_REPLICATION_ON
Start rectification dbms_rectifier_diff.turn_replication_on;
exec dbms_rectifier_diff.turn_replication_on;
 
Demos
Working code from the Morgan's Library office conn sys@pdbdev 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;
/

Related Topics
DBMS_COMPARISON
DBMS_RECTIFIER_DIFF_INTERNAL
DBMS_RECTIFIER_FRIENDS
Packages

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved