16.4.2.1.1 Exceptions

The DEFINE_SITE_PRIORITY procedure may raise the following exceptions:

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.1 Exceptions

The DROP_SITE_PRIORITY procedure may raise the following exceptions:

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.1 Exceptions

The COMMENT_ON_SITE_PRIORITY procedure may raise the following exceptions:

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.1.1 Exceptions

The ADD_SITE_PRIORITY_SITE procedure may raise the following exceptions:

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.1 Exceptions

The DROP_SITE_PRIORITY_SITE procedure may raise the following exceptions:

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.1 Exceptions

The ALTER_SITE_PRIORITY procedure may raise the following exceptions:

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.1 Exceptions

The ALTER_SITE_PRIORITY_SITE procedure may raise the following exceptions:

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