Oracle Built-in Packages

Oracle Built-in PackagesSearch this book
Previous: 16.3 Priority Groups with DBMS_REPCATChapter 16
Conflict Resolution
Next: 16.5 Assigning Resolution Methods with DBMS_REPCAT
 

16.4 Site Priority Groups with DBMS_REPCAT

The site priority group technique resolves conflicts by accepting the data that originated from the site with the highest priority.

16.4.1 About Site Priority Groups

The procedures for creating and maintaining site priority groups are almost completely analogous to those used for priority groups. The similarity arises because a site priority group is actually a special case of a priority group in which the range of data values is the range of global names in the replicated environment. In fact, Oracle stores the information about priority groups and site priority groups in the same data dictionary views (DBA_REPPRIORITY_GROUP and DBA_REPPRIORITY). However, unlike in the priority group technique, you should base site priority group rankings on your confidence in the data from each site, as opposed to the business rules associated with a workflow.

Use the following programs to maintain site priority groups:

DBMS_REPCAT.ADD_SITE_PRIORITY_SITE
DBMS_REPCAT.ALTER_SITE_PRIORITY
DBMS_REPCAT.ALTER_SITE_PRIORITY_SITE
DBMS_REPCAT.COMMENT_ON_SITE_PRIORITY
DBMS_REPCAT.DEFINE_SITE_PRIORITY
DBMS_REPCAT.DROP_SITE_PRIORITY
DBMS_REPCAT.DROP_SITE_PRIORITY_SITE

16.4.2 Creating, Maintaining, and Dropping Site Priorities

DBMS_REPCAT's DEFINE_SITE_PRIORITY and DROP_SITE_PRIORITY procedures allow you to create and drop site priorities. Use the COMMENT_ON_SITE_PRIORITY procedure to maintain the comment on the site priority.

16.4.2.1 The DBMS_REPCAT.DEFINE_SITE_PRIORITY procedure

The DEFINE_SITE_PRIORITY procedure creates a site priority group. You can add sites to this group later. Specifications differ for Oracle7 and Oracle8 as follows.

Here is the Oracle7 specification:

PROCEDURE DBMS_REPCAT.DEFINE_SITE_PRIORITY
   (gname IN VARCHAR2 := '',
    name IN VARCHAR2,
    comment IN VARCHAR2 := NULL,
    sname IN VARCHAR2 := '');

Here is the Oracle8 specification:

PROCEDURE DBMS_REPCAT.DEFINE_SITE_PRIORITY
   (gname IN VARCHAR2 := '',
    name IN VARCHAR2,
    comment IN VARCHAR2 := NULL)

Parameters are summarized in the following table.

Name

Description

gname

Name of the replication group containing the site priority group

name

Name of the site priority group

comment

Comment, visible in DBA_REPPRIORITY_GROUP data dictionary view

sname (Oracle7 only)

Not used

16.4.2.1.1 Exceptions

The DEFINE_SITE_PRIORITY procedure may raise the following exceptions:

Name

Number

Description

duplicateprioritygroup

-23335

Site priority group name already exists

missingrepgroup

-23373

Replication group gname does not exist

nonmasterdef

-23312

Calling site is not the master definition site

16.4.2.1.2 Restrictions

You must call DBMS_REPCAT.DEFINE_SITE_PRIORITY from the master definition site.

16.4.2.1.3 Example

The following call creates a site priority group called SP_NORTH_AMERICA:

	BEGIN
		DBMS_REPCAT.DEFINE_SITE_PRIORITY(
			gname	=> 'SPROCKET',
			name	=> 'SP_NORTH_AMERICA',
			comment	=> 'Site Priority for North American Locations');
	END;

These changes take effect after the next call to DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT for an object in the SPROCKET replication group.

16.4.2.2 The DBMS_REPCAT.DROP_SITE_PRIORITY procedure

The DROP_SITE_PRIORITY procedure drops an existing site priority group that is no longer in use. Specifications differ for Oracle7 and Oracle8 as follows.

Here is the Oracle7 specification:

PROCEDURE DBMS_REPCAT.DROP_SITE_PRIORITY
   (gname IN VARCHAR2 := '',
    name IN VARCHAR2,
    sname IN VARCHAR2 := '');

Here is the Oracle8 specification:

PROCEDURE DBMS_REPCAT.DROP_SITE_PRIORITY
   (gname IN VARCHAR2 := '',
    name IN VARCHAR2)

Parameters are summarized in the following table.

Name

Description

gname

Name of the replication group containing the site priority group

name

Name of the site priority group

sname (Oracle7 only)

Not used

WARNING: As with the DROP_PRIORITY_GROUP procedure, do not attempt to drop a site priority group that is acting as an UPDATE conflict resolution handler for a column group. First, use DROP_UPDATE_RESOLUTION to drop the conflict handler for the column group.

16.4.2.2.1 Exceptions

The DROP_SITE_PRIORITY procedure may raise the following exceptions:

Name

Number

Description

missingrepgroup

-23373

Replication group gname does not exist

nonmasterdef

-23312

Calling site is not the master definition site

referenced

-23332

Site priority group is used by existing conflict resolution method

16.4.2.2.2 Restrictions

You must call DBMS_REPCAT.DROP_SITE_PRIORITY from the master definition site.

16.4.2.2.3 Example

The following example shows how to drop a site priority group that is no longer in use:

	BEGIN
		DBMS_REPCAT.DROP_SITE_PRIORITY(
			gname	=> 'SPROCKET',
			name	=> 'SP_NORTH_AMERICA');
	END;

16.4.2.3 The DBMS_REPCAT.COMMENT_ON_SITE_PRIORITY procedure

The COMMENT_ON_SITE_PRIORITY procedure creates or replaces the comment field in the DBA_REPPRIORITY_GROUP data dictionary view for the specified site priority group. Specifications differ for Oracle7 and Oracle8 as follows.

Here is the Oracle7 specification:

PROCEDURE DBMS_REPCAT.COMMENT_ON_SITE_PRIORITY
   (gname IN VARCHAR2 := '',
    name IN VARCHAR2,
    comment IN VARCHAR2,
    sname IN VARCHAR2 := '');

Here is the Oracle8 specification:

PROCEDURE DBMS_REPCAT.COMMENT_ON_SITE_PRIORITY
   (gname IN VARCHAR2 := '',
    name IN VARCHAR2,
    comment IN VARCHAR2)

Parameters are summarized in the following table.

Name

Description

gname

Name of the replication group containing the priority group

name

Name of the site priority group

comment

Comment

sname (Oracle7 only)

Not used

16.4.2.3.1 Exceptions

The COMMENT_ON_SITE_PRIORITY procedure may raise the following exceptions:

Name

Number

Description

missingpriority

-1403

Site priority group name does not exist

missingrepgroup

-23373

Replication group gname does not exist

nonmasterdef

-23312

Calling site is not master definition site

16.4.2.3.2 Restrictions

You must call DBMS_REPCAT.COMMENT_ON_SITE_PRIORITY from the master definition site.

16.4.2.3.3 Example

The following example shows how to replace a comment on a site priority group:

	BEGIN
		DBMS_REPCAT.COMMENT_ON_SITE_PRIORITY(
			gname	=> 'SPROCKET',
			name	=> 'SP_NORTH_AMERICA',
			comment	=> 'Comment added on '||sysdate|| ' by '|| user);
	END;

16.4.3 Maintaining Site Priorities

Once you have created a site priority group as described in the previous section, you can add and drop sites (with ADD_SITE_PRIORITY_SITE and DROP_SITE_PRIORITY_SITE) and modify their priority values (with ALTER_SITE_PRIORITY and ALTER_SITE_PRIORITY_SITE).

You can query the data dictionary table DBA_REPPRIORITY for information about site priority group members and their priorities.

16.4.3.1 The DBMS_REPCAT.ADD_SITE_PRIORITY_SITE procedure

The ADD_SITE_PRIORITY_SITE procedure adds a new site to a site priority group. Specifications for Oracle7 and Oracle8 differ as follows.

Here is the Oracle7 specification:

PROCEDURE DBMS_REPCAT.ADD_SITE_PRIORITY_SITE
   (gname IN VARCHAR2 := '',
    name IN VARCHAR2,
    site IN VARCHAR2,
    priority IN NUMBER,
    sname IN VARCHAR2 := '');

Here is the Oracle8 specification:

PROCEDURE DBMS_REPCAT.ADD_SITE_PRIORITY_SITE
   (gname IN VARCHAR2 := '',
    name IN VARCHAR2,
    site IN VARCHAR2,
    priority IN NUMBER);

Parameters are summarized in the following table.

Name

Description

gname

Name of the replication group to which site priority group name belongs

name

Name of the site priority group

site

Global name of the new site

priority

Priority designated to site

sname (Oracle7 only)

Not used

16.4.3.1.1 Exceptions

The ADD_SITE_PRIORITY_SITE procedure may raise the following exceptions:

Name

Number

Description

duplicatepriority

-23335

Another site is already designated with priority specified by priority parameter

duplicatesite

-23338

Site is already in the site priority group name

missingpriority

-1403

Site does not exist

missingrepgroup

-23373

Replication group gname does not exist

nonmasterdef

-23312

Calling site is not the master definition site

16.4.3.1.2 Restrictions

Note the following restrictions on calling ADD_SITE_PRIORITY_SITE:

  • You must call the ADD_SITE_PRIORITY_SITE procedure from the master definition site.

  • The new priority must be unique within the site priority group.

16.4.3.1.3 Example

This example adds four sites to the site priority group SP_NORTH_AMERICA:

	BEGIN
		DBMS_REPCAT.ADD_SITE_PRIORITY_SITE(
			gname		=> 'SPROCKET',
			name		=> 'SP_NORTH_AMERICA',
			site		=> 'D7OH.BIGWHEEL.COM',
			priority	=> 10);
		DBMS_REPCAT.ADD_SITE_PRIORITY_SITE(
			gname		=> 'SPROCKET',
			name		=> 'SP_NORTH_AMERICA',
			site		=> 'D7NY.BIGWHEEL.COM',
			priority	=> 20);
		DBMS_REPCAT.ADD_SITE_PRIORITY_SITE(
			gname		=> 'SPROCKET',
			name		=> 'SP_NORTH_AMERICA',
			site		=> 'D7HI.BIGWHEEL.COM',
			priority	=> 30);
		DBMS_REPCAT.ADD_SITE_PRIORITY_SITE(
			gname		=> 'SPROCKET',
			name		=> 'SP_NORTH_AMERICA',
			site		=> 'D7CA.BIGWHEEL.COM',
			priority	=> 40);
	END;

TIP: As with the ADD_PRIORITY_<datatype> procedure, it is a good idea to use multiples of 10 or more for the priority values so that you have some flexibility for future changes and additions.

After making these calls, we can query DBA_REPPRIORITY to confirm that Oracle added the sites.


SQL>  SELECT		gname,
  2  			priority_group,
  3  			varchar2_value		site_name,
  4  			priority
  5  FROM		dba_reppriority
  6  WHERE		priority_group = 'SP_NORTH_AMERICA'
  7  ORDER BY		priority;

GNAME	 PRIORITY_GROUP      SITE_NAME 	   	  PRIORITY
-------- -------------------- -------------------- --------
SPROCKET SP_NORTH_AMERICA     D7OH.BIGWHEEL.COM 	10
SPROCKET SP_NORTH_AMERICA     D7NY.BIGWHEEL.COM 	20
SPROCKET SP_NORTH_AMERICA     D7HI.BIGWHEEL.COM 	30

Although DBA_REPPRIORITY reflects the changes from the ADD_SITE_PRIORITY_SITE call, you must call DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT for an object in the replication group for the changes to propagate to the other master sites. This is the case for all of the DBMS_REPCAT procedures pertaining to site priority groups.

16.4.3.2 The DBMS_REPCAT.DROP_SITE_PRIORITY_SITE procedure

The DROP_SITE_PRIORITY_SITE procedure removes a site from a site priority group. Specifications for Oracle7 and Oracle8 differ as follows.

Here is the Oracle7 specification:

PROCEDURE DBMS_REPCAT.DROP_SITE_PRIORITY_SITE
  (gname IN VARCHAR2 := '',
   name IN VARCHAR2,
   site IN VARCHAR2,
   sname IN VARCHAR2 := '');

Here is the Oracle8 specification:

PROCEDURE DBMS_REPCAT.DROP_SITE_PRIORITY_SITE
   (gname IN VARCHAR2 := '',
    name IN VARCHAR2,
    site IN VARCHAR2);

Parameters are summarized in the following table.

Name

Description

gname

Name of the replication group to which site priority group name belongs

name

Name of the site priority group

site

Global name of the new site

sname (Oracle7 only)

Not used

16.4.3.2.1 Exceptions

The DROP_SITE_PRIORITY_SITE procedure may raise the following exceptions:

Name

Number

Description

missingpriority

-1403

Site priority does not exist

missingrepgroup

-23373

Replication group gname does not exist

nonmasterdef

-23312

Calling site is not the master definition site

16.4.3.2.2 Restrictions

You must call DROP_SITE_PRIORITY_SITE from the master definition site.

16.4.3.2.3 Example

Here we drop D7TX.BIGWHEEL.COM from the SP_NORTH_AMERICA site priority:

	BEGIN
		DBMS_REPCAT.DROP_SITE_PRIORITY_SITE(
			gname	=> 'SPROCKET',
			name	=> 'SP_NORTH_AMERICA',
			site	=> 'D7TX.BIGWHEEL.COM');
	END;

As with the other site priority procedures, you must call GENERATE_REPLICATION_SUPPORT for an object in the replication group to propagate this change to other master sites.

16.4.3.3 The DBMS_REPCAT.ALTER_SITE_PRIORITY procedure

Just as you can change the priority of a value in a priority group, you can change the priority of a site in a site priority group. Use the ALTER_SITE_PRIORITY procedure to do this. The specifications for Oracle7 and Oracle8 differ as follows.

Here is the Oracle7 specification:

PROCEDURE DBMS_REPCAT.ALTER_SITE_PRIORITY
   (gname IN VARCHAR2 := '',
    name IN VARCHAR2,
    old_priority IN NUMBER,
    new_priority IN NUMBER,
    sname IN VARCHAR2 := '');

Here is the Oracle8 specification:

PROCEDURE DBMS_REPCAT.ALTER_SITE_PRIORITY
   (gname IN VARCHAR2 := '',
    name IN VARCHAR2,
    old_priority IN NUMBER,
    new_priority IN NUMBER);
    site IN VARCHAR2);

Parameters are summarized in the following table.

Name

Description

gname

Name of the replication group to which the site priority group name belongs

name

Name of the site priority group

old_priority

Site's current priority

new_priority

Site's new priority

sname (Oracle7 only)

Not used

16.4.3.3.1 Exceptions

The ALTER_SITE_PRIORITY procedure may raise the following exceptions:

Name

Number

Description

duplicatepriority

-1

Priority new_priority already exists for the site priority group name

missingpriority

-1403

Priority old_priority is not associated with any sites

missingrepgroup

-23373

Replication group gname does not exist

missingvalue

-23337

Value old_value does not already exist

nonmasterdef

-23312

Calling site is not the master definition site

paramtype

-23325

Parameter new_value is incorrect datatype

16.4.3.3.2 Restrictions

Note the following restrictions on calling ALTER_SITE_PRIORITY:

  • You must run this procedure from the master definition site.

  • The new priority must be unique within the site priority group.

16.4.3.3.3 Example

In this example, we move D7NY.BIGWHEEL.COM (from a previous example) to a higher priority, between D7HI.BIGWHEEL.COM and D7CA.BIGWHEEL.COM:

	BEGIN
		DBMS_REPCAT.ALTER_SITE_PRIORITY(
			gname		=> 'SPROCKET',
			name		=> 'SP_NORTH_AMERICA',
			old_priority	=> 20,
			new_priority	=> 35);
	END;

And, querying the DBA_REPPRIORITY data dictionary view again, we see the changed data (shown here in boldface):

SQL>  SELECT		gname,
  2  			priority_group,
  3  			varchar2_value		site_name,
  4  			priority
  5  FROM		dba_reppriority
  6  WHERE		priority_group = 'SP_NORTH_AMERICA'
  7  ORDER BY		priority;

GNAME	 PRIORITY_GROUP      SITE_NAME 	   	  PRIORITY
-------- -------------------- -------------------- --------
SPROCKET SP_NORTH_AMERICA     D7OH.BIGWHEEL.COM 	10
SPROCKET SP_NORTH_AMERICA     D7HI.BIGWHEEL.COM 	30
SPROCKET SP_NORTH_AMERICA     D7NY.BIGWHEEL.COM 	35
SPROCKET SP_NORTH_AMERICA     D7CA.BIGWHEEL.COM 	40

This shows D7NY.BIGWHEEL.COM with its new and higher priority.

16.4.3.4 The DBMS_REPCAT.ALTER_SITE_PRIORITY_SITE

The ALTER_SITE_PRIORITY_SITE procedure is analogous to the DBMS_REPCAT.ADD_PRIORITY_<datatype> procedure; use it to change the site name for an existing named site in a site priority group. The specifications for Oracle7 and Oracle8 differ as follows.

Here is the Oracle7 specification:

PROCEDURE DBMS_REPCAT.ALTER_SITE_PRIORITY_SITE
   (gname IN VARCHAR2 := '',
    name IN VARCHAR2,
    old_site IN VARCHAR2,
    new_site IN VARCHAR2,
    sname IN VARCHAR2 := '');

Here is the Oracle8 specification:

PROCEDURE DBMS_REPCAT.ALTER_SITE_PRIORITY_SITE
   (gname IN VARCHAR2 := '',
    name IN VARCHAR2,
    old_site IN VARCHAR2,
    new_site IN VARCHAR2);

Parameters are summarized in the following table:

Name

Description

gname

Name of the replication group to which the site priority group name belongs

name

Name of the site priority group

old_site

Global name of the site currently associated with the priority level

new_site

Global name of the site that is to replace old_site at old_site's priority level

sname (Oracle7 only)

Not used

16.4.3.4.1 Exceptions

The ALTER_SITE_PRIORITY_SITE procedure may raise the following exceptions:

Name

Number

Description

duplicatesite

-1

new_site is already in the site priority group

missingpriority

-1403

Site priority group name does not exist

missingrepgroup

-23373

Replication group gname does not exist

missingvalue

-23337

old_site is not in the site priority group

nonmasterdef

-23312

Calling site is not the master definition site

16.4.3.4.2 Restrictions

Note the following restrictions on calling ALTER_SITE_PRIORITY_SITE:

  • You must call this procedure from the master definition site.

  • The new site must be unique in the site priority group.

16.4.3.4.3 Example

In this example, we replace the site associated with priority level 10 with D7TX.BIGWHEEL.COM:

	BEGIN
		DBMS_REPCAT.ALTER_SITE_PRIORITY_SITE(
			gname		=> 'SPROCKET',
			name		=> 'SP_NORTH_AMERICA',
			old_site	=> 'D7OH.BIGWHEEL.COM',
			new_site	=> 'D7TX.BIGWHEEL.COM');
	END;

And again, we can see the change in DBA_REPPRIORITY:


SQL>  SELECT		gname,
  2  			priority_group,
  3  			varchar2_value		site_name,
  4  			priority
  5  FROM		dba_reppriority
  6  WHERE		priority_group = 'SP_NORTH_AMERICA'
  7  ORDER BY		priority;

GNAME	 PRIORITY_GROUP      SITE_NAME 	   	  PRIORITY
-------- -------------------- -------------------- --------
SPROCKET SP_NORTH_AMERICA     D7TX.BIGWHEEL.COM 	10
SPROCKET SP_NORTH_AMERICA     D7HI.BIGWHEEL.COM         30
SPROCKET SP_NORTH_AMERICA     D7NY.BIGWHEEL.COM         35
SPROCKET SP_NORTH_AMERICA     D7CA.BIGWHEEL.COM         40


Previous: 16.3 Priority Groups with DBMS_REPCATOracle Built-in PackagesNext: 16.5 Assigning Resolution Methods with DBMS_REPCAT
16.3 Priority Groups with DBMS_REPCATBook Index16.5 Assigning Resolution Methods with DBMS_REPCAT

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
This HTML Help has been published using the chm2web software.