Oracle DBMS_RECTIFIER


	CODE Oracle Code Library


	JOBS Find Or Post Oracle Jobs


	FORUM Oracle Discussion & Chat

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