CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_RECTIFIER_DIFF
Version 11.1
 
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;
 
Related Topics
DBMS_COMPARISON
DBMS_RECTIFIER_FRIENDS
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [126 users online]    © 2010 psoug.org