The DBMS_RECTIFIER_DIFF
package contains APIs used to detect and resolve data inconsistencies between two replicated sites.
This chapter contains this topic:
Table 16-1 DBMS_RECTIFIER_DIFF Package Subprograms
Subprogram | Description |
---|---|
Determines the differences between two tables. |
|
Resolves the differences between two tables. |
This procedure determines the differences between two tables. It accepts the storage table of a nested table.
Note:
This procedure cannot be used on LOB columns, nor on columns based on user-defined types.DBMS_RECTIFIER_DIFF.DIFFERENCES ( sname1 IN VARCHAR2, oname1 IN VARCHAR2, reference_site IN VARCHAR2 := '', sname2 IN VARCHAR2, oname2 IN VARCHAR2, comparison_site IN VARCHAR2 := '', where_clause IN VARCHAR2 := '', { column_list IN VARCHAR2 := '', | array_columns IN DBMS_UTILITY.NAME_ARRAY, } missing_rows_sname IN VARCHAR2, missing_rows_oname1 IN VARCHAR2, missing_rows_oname2 IN VARCHAR2, missing_rows_site IN VARCHAR2 := '', max_missing IN INTEGER, commit_rows IN INTEGER := 500);
Note:
This procedure is overloaded. Thecolumn_list
and array_columns
parameters are mutually exclusive.Table 16-2 DIFFERENCES Procedure Parameters
Parameter | Description |
---|---|
sname1 |
Name of the schema at |
oname1 |
Name of the table at |
reference_site |
Name of the reference database site. The default, |
sname2 |
Name of the schema at |
oname2 |
Name of the table at |
comparison_site |
Name of the comparison database site. The default, |
where_clause |
Only rows satisfying this clause are selected for comparison. The default, |
column_list |
A comma-delimited list of one or more column names being compared for the two tables. You must not have any spaces before or after a comma. The default, |
array_columns |
A PL/SQL index-by table of column names being compared for the two tables. Indexing begins at 1, and the final element of the array must be |
missing_rows_sname |
Name of the schema containing the tables with the missing rows. |
missing_rows_oname1 |
Name of an existing table at |
missing_rows_oname2 |
Name of an existing table at |
missing_rows_site |
Name of the site where the |
max_missing |
Integer that specifies the maximum number of rows that should be inserted into the |
commit_rows |
Maximum number of rows to insert to or delete from the reference or comparison table before a |
Table 16-3 DIFFERENCES Procedure Exceptions
Exception | Description |
---|---|
nosuchsite |
Database site could not be found. |
badnumber |
The |
missingprimarykey |
Column list must include primary key (or |
badname |
|
cannotbenull |
Parameter cannot be |
notshapeequivalent |
Tables being compared are not shape equivalent. Shape refers to the number of columns, their column names, and the column datatypes. |
unknowncolumn |
Column does not exist. |
unsupportedtype |
Type not supported. |
dbms_repcat.commfailure |
Remote site is inaccessible. |
dbms_repcat.missingobject |
Table does not exist. |
The error ORA-00001
(unique constraint violated) is issued when there are any unique or primary key constraints on the missing rows table.
This procedure resolves the differences between two tables. It accepts the storage table of a nested table.
Note:
This procedure cannot be used on LOB columns, nor on columns based on user-defined types.DBMS_RECTIFIER_DIFF.RECTIFY ( sname1 IN VARCHAR2, oname1 IN VARCHAR2, reference_site IN VARCHAR2 := '', sname2 IN VARCHAR2, oname2 IN VARCHAR2, comparison_site IN VARCHAR2 := '', { column_list IN VARCHAR2 := '', | array_columns IN dbms_utility.name_array, } missing_rows_sname IN VARCHAR2, missing_rows_oname1 IN VARCHAR2, missing_rows_oname2 IN VARCHAR2, missing_rows_site IN VARCHAR2 := '', commit_rows IN INTEGER := 500);
Note:
This procedure is overloaded. Thecolumn_list
and array_columns
parameters are mutually exclusive.Table 16-4 RECTIFY Procedure Parameters
Parameter | Description |
---|---|
sname1 |
Name of the schema at |
oname1 |
Name of the table at |
reference_site |
Name of the reference database site. The default, |
sname2 |
Name of the schema at |
oname2 |
Name of the table at |
comparison_site |
Name of the comparison database site. The default, |
column_list |
A comma-delimited list of one or more column names being compared for the two tables. You must not have any spaces before or after a comma. The default, |
array_columns |
A PL/SQL index-by table of column names being compared for the two tables. Indexing begins at 1, and the final element of the array must be |
missing_rows_sname |
Name of the schema containing the tables with the missing rows. |
missing_rows_oname1 |
Name of the table at |
missing_rows_oname2 |
Name of the table at |
missing_rows_site |
Name of the site where the |
commit_rows |
Maximum number of rows to insert to or delete from the reference or comparison table before a |
Table 16-5 RECTIFY Procedure Exceptions
Exception | Description |
---|---|
nosuchsite |
Database site could not be found. |
badnumber |
The |
badname |
|
dbms_repcat.commfailure |
Remote site is inaccessible. |
dbms_repcat.missingobject |
Table does not exist. |