Oracle Built-in Packages

Oracle Built-in PackagesSearch this book
Previous: 15.4 DBMS_OFFLINE_OG: Performing Site InstantiationChapter 15
Advanced Replication
Next: 15.6 DBMS_REPUTIL: Enabling and Disabling Replication
 

15.5 DBMS_RECTIFIER_DIFF: Comparing Replicated Tables

If you are not sure whether the data at two sites are identical, you can use the DBMS_RECTIFIER_DIFF package to find out. The DIFFERENCES procedure compares the data in a table at a master site with the same table at a reference site. After determining the differences, you can use DBMS_RECTIFIER_DIFF.RECTIFY to synchronize the tables.

15.5.1 Getting Started with DBMS_RECTIFIER_DIFF

The DBMS_RECTIFIER_DIFF package is created when the Oracle database is installed. The dbmsrepc.sql script (found in the built-in packages source directory, as described in Chapter 1) contains the source code for this package's specification. This script is called by catrep.sql, which must be run to install the advanced replication packages. The wrapped sql script prvtrctf.sql creates the public synonym DBMS_RECTIFIER_DIFF. No EXECUTE privileges are granted on DBMS_RECTIFIER_DIFF; only the owner (SYS) and those with the EXECUTE ANY PROCEDURE system privilege may execute the package.

Table 15.6 summarizes the DBMS_RECTIFIER_DIFF programs.


Table 15.6: DBMS_RECTIFIER_DIFF.DIFFERENCES Programs

Name

Description

Use in SQL?

DIFFERENCES

Determines differences between truth table and comparison table

No

RECTIFY

Synchronizes comparison table with truth table

No

15.5.2 DBMS_RECTIFIER_DIFF Interface

This section describes the programs available in the DBMS_RECTIFIER_DIFF package.

15.5.2.1 The DBMS_RECTIFIER.DIFFERENCES procedure

The DIFFERENCES procedure compares the data in a table at a master site with the same table at a reference site. The reference need not be the master definition site.

The procedure stores discrepancies between the reference table and comparison table in a "missing rows" table, which the user must create. It populates the table specified by the missing_rows_oname1 parameter with rows that exist in the reference table but not the comparison table, and rows that exist in the comparison table but not the reference table. The table identified by the missing_rows_oname2 parameter has one record for every record in missing_rows_oname1, which identifies which site has the record.

Here is the specification:

PROCEDURE 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);

Parameters are summarized in the following table.

Name

Description

sname1

Name of schema that owns oname1.

oname1

Table at reference_site (truth table).

reference_site

The global_name of site with truth table. If NULL or ` ' (default), truth table is assumed to be local.

sname2

Name of schema that owns oname2.

oname2

The comparison table.

comparison_site

or ` ', table is assumed to be local.

where_clause

Optional predicate that can be used to limit set of rows compared (e.g.,`WHERE STATE = `CA'').

column_list

Comma-separated list of one or more columns whose values are to be compared. If NULL or ` ' (default), then all columns are used. There should not be any whitespace after the commas.

array_columns

PL/SQL table of column names; either column_list or array_columns can be passed, not both.

missing_rows_sname

Name of schema that owns missing_rows_oname1.

missing_rows_oname1

Name of table containing records that do not exist in both truth table and comparison table.

missing_rows_oname2

Table that holds information telling which table owns each record in missing_rows_oname1.

missing_rows_site

The global_name of site where tables missing_rows_oname1 and missing_rows_oname2 exist; if NULL or ` ' (default), tables are assumed to be local.

max_missing

The maximum number or rows to insert into missing_rows_oname1 before exiting; can be any value > 1.

comming_rows

Commit rows inserted into missing_row_oname1 after this many records.

15.5.2.1.1 Exceptions

The DIFFERENCES procedure may raise the following exceptions:

Name

Number

Description

badmrname

-23377

The oname1 is the same as missing_rows_oname1

badname

-23368

The sname, oname, missing_rows_sname,

or missing_rows_oname is NULL or ` '

badnumber

-23366

The max_missing is less than 1 or NULL

dbms_repcat.commfailure

-23302

Remote site is not accessible

dbms_repcat.missingobject

-23308

The tables oname1, oname2, missing_rows_oname1, or missing_rows_oname2 do not exist

nosuchsite

-23365

The reference_site, comparison_site, or missing_rows_site does not name a site

15.5.2.1.2 Restrictions

Note the following restrictions on calling the DIFFERENCES procedure:

  • You must create tables missing_rows_sname.missing_rows_oname1 and missing_rows_sname.missing_rows_oname2 before running this procedure.

  • The columns in table missing_rows_oname1 must match the columns passed to column_list or array_columns exactly.

  • The replication group to which the tables belong must be quiesced.

15.5.2.1.3 Example

For an example of how to use the DIFFERENCES procedure, see the example under the RECTIFY procedure.

15.5.2.2 The DBMS_RECTIFIER_DIFF.RECTIFY procedure

The DIFFERENCES procedure paves the way for its companion procedure, RECTIFY, which synchronizes the reference table. Before running the RECTIFY procedure, always make sure that the updates to the comparison table will not violate any integrity, check, or NOT NULL constraints. Note that this procedure does not modify the reference table. Here's the specification:

PROCEDURE 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);

Parameters are summarized in the following table.

Name

Description

sname1

Name of schema that owns oname1.

oname1

Table at reference_site (truth table).

reference_site

The global_name of site with truth table; if NULL or ` ' (default), truth table is assumed to be local.

sname2

Name of schema that owns oname2.

oname2

The comparison table.

comparison_site

The global_name of the site with comparison table. If NULL

or ` ', table is assumed to be local.

column_list

A comma-separated list of one or more columns whose values are to be compared; if NULL or ` ' (default), then all columns are used. There should not be any white space after the commas.

array_columns

PL/SQL table of column names; either column_list or array_columns can be passed, not both.

missing_rows_sname

Name of schema that owns missing_rows_oname1.

missing_rows_oname1

The name of the table containing records that do not exist in both truth table and comparison table.

missing_rows_oname2

The table that holds information telling which table owns each record in missing_rows_oname1.

missing_rows_site

The global_name of the site where tables missing_rows_oname1 and missing_rows_oname2 exist; if NULL or ` ' (default), tables are assumed to be local.

comming_rows

Commit rows inserted into missing_row_oname1 after this many records.

15.5.2.2.1 Exceptions

The RECTIFY procedure may raise the following exceptions:

Name

Number

Description

badname

-23368

The sname, oname, missing_rows_sname, or missing_rows_oname is NULL or ` '

badnumber

-23366

The max_missing is less than 1 or NULL

dbms_repcat.commfailure

-23302

Remote site is not accessible

dbms_repcat.missingobject

-23308

The tables oname1, oname2, missing_rows_oname1, or missing_rows_oname2 do not exist

dbms_repcat.norepoption

-2094

Replication option is not linked to kernel

nosuchsite

-23365

The reference_site, comparison_site, or missing_rows_site does not name a site

15.5.2.2.2 Restrictions

Note the following restrictions on calling RECTIFY:

  • The DIFFERENCES procedure must have been run prior to running RECTIFY.

  • The replication group to which the tables belong should still be quiesced.

  • If duplicate rows exist in the reference table but not the comparison table they will be inserted into the comparison table.

  • If duplicate rows exist in the comparison table but not the reference table they will be deleted from the comparison table.

15.5.2.2.3 Example

Assume that the table SPROCKET.DAILY_SALES is replicated between sites D7CA.BIGWHEEL.COM (the references site) and D7NY.BIGWHEEL.COM (the comparison site). The following table shows the description of the DAILY_SALES table.

Column Name

Data Type

sales_id

NUMBER(9)

distributor_id

NUMBER(6)

product_id

NUMBER(9)

units

NUMBER(9,2)

The following steps executed at D7CA.BIGWHEEL.COM would populate the tables missing_rows_daily_sales and missing_location_daily_sales and rectify these differences. These steps should be executed under the designated replication administrator account. Note that storage parameters are left out of the example for the sake of brevity and clarity, but they should be included whenever you run the DBMS_RECTIFIER_DIFF.DIFFERENCES procedure.


	CREATE TABLE missing_rows_daily_sales (
	sales_id	NUMBER(9),
	distributor_id	NUMBER(6),
	product_id	NUMBER(9),
	units		NUMBER(9,2)
	);

	CREATE TABLE missing_location_daily_sales (
	present	VARCHAR2(128),
	absent	VARCHAR2(128),
	r_id	ROWID
	);

	BEGIN
		DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY('SPROCKET');

		DBMS_RECTIFIER_DIFF.DIFFERENCES( -
			sname1			=> 'SPROCKET', 
			oname1			=> 'DAILY_SALES', 
			reference_site		=> 'D7CA.BIGWHEEL.COM', 
			sname2			=> 'SPROCKET', 
			oname2			=> 'SPROCKET', 
			comparison_site		=> 'D7NY.BIGWHEEL.COM', 
			where_clause		=> NULL, 
			column_list		=> 'SALES_ID,DISTRIBUTOR_ID,PRODUCT_		            							  ID,UNITS', 
			missing_rows_sname	=> 'REPADMIN', 
			missing_rows_oname1	=> 'MISSING_ROWS_DAILY_SALES', 
			missing_rows_oname2	=> 'MISSING_LOCATIONS_DAILY_SALES ,
			missing_rows_site	=> 'D7CA.BIGWHEEL.COM', 
			max_missing		=> 500, 
			comit_rows		=> 100);

		DBMS_RECTIFIER_DIFF.RECTIFY( -
			sname1			=> 'SPROCKET', 
			oname1			=> 'DAILY_SALES', 
			reference_site		=> 'D7CA.BIGWHEEL.COM', 
			sname2			=> 'SPROCKET', 
			oname2			=> 'SPROCKET', 
			comparison_site		=> 'D7NY.BIGWHEEL.COM', 
						       ID,UNITS', 
			missing_rows_sname	=> 'REPADMIN', 
			missing_rows_oname1	=> 'MISSING_ROWS_DAILY_SALES', 
			missing_rows_oname2	=> 'MISSING_LOCATIONS_DAILY_SALES -
			missing_rows_site	=> 'D7CA.BIGWHEEL.COM', 
			comit_rows		=> 100);
	END;

TIP: These procedures can take a long time to run. If the volume of data is significant, it will probably be easier for you to simply reinstantiate the comparison table by importing an export of the reference table.


Previous: 15.4 DBMS_OFFLINE_OG: Performing Site InstantiationOracle Built-in PackagesNext: 15.6 DBMS_REPUTIL: Enabling and Disabling Replication
15.4 DBMS_OFFLINE_OG: Performing Site InstantiationBook Index15.6 DBMS_REPUTIL: Enabling and Disabling Replication

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference