[Chapter 14] 14.2 DBMS_REFRESH: Managing Snapshot Groups

14.2 DBMS_REFRESH: Managing Snapshot Groups

The DBMS_REFRESH package contains procedures for administrating snapshot groups. A snapshot group is a collection of one or more snapshots that Oracle refreshes in an atomic transaction, guaranteeing that relationships among the master tables are preserved in the snapshot tables. The DBMS_REFRESH package includes packages that perform the following functions:

Create and destroy snapshot groups
Add and subtract snapshots from snapshot groups
Manually refresh snapshot groups
Change properties of the snapshot group, such as the refresh interval

Figure Figure 14.1 shows how DBMS_REFRESH works and Figure Figure 14.2 illustrates snapshot groups.

Figure 14.1: DBMS_REFRESH components

Figure 14.2: A snapshot group

14.2.1 Getting Started with DBMS_REFRESH

The DBMS_REFRESH package is created when the Oracle database is installed. The dbmssnap.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 catproc.sql , which is normally run immediately after database creation. The script creates the public synonym DBMS_REFRESH for the package and grants EXECUTE privilege on the package to public. All Oracle users can reference and make use of this package.

Table 14-3 lists the programs available in the DBMS_REFRESH package.

Table 14.3: DBMS_REFRESH Programs
Name	Description	Use in SQL?
ADD	Adds one or more snapshots to an existing refresh group	No
CHANGE	Changes parameters associated with a refresh group	No
DESTROY	Removes a refresh group	No
MAKE	Creates a refresh group	No
REFRESH	Forces a refresh of a refresh group	No
SUBTRACT	Removes one or more snapshots from a refresh group	No

DBMS_REFRESH does not define any exceptions.

14.2.2 Creating and Destroying Snapshot Groups

The MAKE and DESTROY procedures create and destroy snapshot groups, respectively. You call these procedures from the snapshot site.

14.2.2.1 The DBMS_REFRESH.MAKE procedure

Call the MAKE procedure to create a snapshot group. Note that you must select either the list or tab parameter, but not both. The specifications for Oracle7 and Oracle8 versions differ as follows.

Here is the Oracle7 specification:

PROCEDURE DBMS_REFRESH.MAKE
   (name IN VARCHAR2,
   {list IN VARCHAR2,| tab IN dbms_utility.uncl_array,}
    next_date IN DATE,
    interval IN VARCHAR2,
    implicit_destroy IN BOOLEAN DEFAULT FALSE,
    lax IN BOOLEAN DEFAULT FALSE,
    job IN BINARY_INTEGER DEFAULT 0,
    rollback_seg IN VARCHAR2 DEFAULT NULL,
    push_deferred_rpc IN BOOLEAN DEFAULT TRUE,
    refresh_after_errors IN BOOLEAN DEFAULT FALSE );

Here is the Oracle8 specification:

PROCEDURE DBMS_REFRESH.MAKE
   (name IN VARCHAR2,
   {list IN VARCHAR2,| tab IN dmbs_utility.uncl_array,}
    next_date IN DATE,
    interval IN VARCHAR2,
    implicit_destroy IN BOOLEAN := FALSE,
    lax IN BOOLEAN := FALSE,
    job IN BINARY_INTEGER := 0,
    rollback_seg IN VARCHAR2 := NULL,
    push_deferred_rpc IN BOOLEAN := TRUE,
    refresh_after_errors IN BOOLEAN := FALSE,
    purge_option  IN BINARY_INTEGER := 1,
    parallelism IN BINARY_INTEGER := 0,
    heap_size IN BINARY_INTEGER := 0);

In both Oracle7 and Oracle8, the MAKE procedure is overloaded; you can supply the list of snapshots either as a comma-separated string with the list parameter, or as a PL/SQL table with the tab parameter.

Parameters are summarized in the following table.

Name	Description
name	Name of the refresh group to create.
list	A comma-delimited string of snapshots to include in the new refresh group. Use either list or tab to specify the snapshot(s) you want to add.
tab	A PL/SQL table of snapshots to include in the new refresh group. Use either list or tab to specify the snapshot(s) you want to add.
next_date	The time of the next refresh.
interval	A DATE expression indicating the snapshot group's refresh interval.
implicit_destroy	If set to TRUE, the snapshot group is destroyed if all snapshots are removed from it.
lax	If set to TRUE and the snapshot(s) already exist in a refresh group other than name, the snapshot(s) are first removed from the other group.
job	Used by import utility. Always use default value of 0.
rollback_seg	Specifies the rollback segment to use during snapshot refreshes. If set to NULL, the default rollback segment is used.
push_deferred_rpc	For updateable snapshots only. Setting this parameter to TRUE indicates that local updates will be pushed back to the master site (otherwise, local updates will not be visible during the refresh).
refresh_after_errors	For updateable snapshots only. Setting this parameter to TRUE indicates that refreshes should occur even if errors exist in the DEFERROR data dictionary view.
purge_option (Oracle8 only)	If push_ deferred_rpc is TRUE, this designates the purge method; default is 1. 0 No purge 1 Lazy purge (optimized for time) 2 Aggressive purge (complete)
parallelism (Oracle8 only)	If push_defered_rpc is TRUE, this determines the maximum degree of parallelism; default is 1. 0 Serial 1 Parallel with 1 slave N Parallel with N slaves (N > 1)
heap_size (Oracle8 only)	Used only if parallelism > 0. Sets the maximum number of transactions to be examined simultaneously for determining parallel scheduling. Oracle determines this value internally; you are advised not to use it.

The MAKE procedure does not raise any exceptions.

14.2.2.1.1 Examples

The following examples illustrate how the MAKE procedure may be used.

14.2.2.1.2 Read-only snapshot. group

In this example, we create a snapshot refresh group of read-only snapshots:

DECLARE
vSnapshotList dbms_utility.uncl_array
BEGIN
    vSnapshotList(1) = 'COUNTRIES'
    vSnapshotList(2) = 'STATES'
    vSnapshotList(3) = 'POSTAL_CODES'
    vSnapshotList(4) = 'CUSTOMER_ADDRESSES'

    DBMS_REFRESH.MAKE(name => 'SG_ADDR_TABS',
                      tab => vSnapShotList,
                      next_date => TRUNC(sysdate) + 1,
                      interval => 'SYSDATE + 1');
END;

This example shows the simplest invocation of DBMS_REFRESH.MAKE; defaults are used for all possible parameters. This call creates a snapshot group on four related tables, and schedules them to be refreshed at every day at midnight.

14.2.2.1.3 Read-only snapshot group with specialized parameters

In the following example, we create a snapshot refresh group of read-only snapshots with specialized parameters:

DECLARE
vSnapshotList dbms_utility.uncl_array
BEGIN
    vSnapshotList(1) = 'COUNTRIES'
    vSnapshotList(2) = 'STATES'
    vSnapshotList(3) = 'POSTAL_CODES'
    vSnapshotList(4) = 'CUSTOMER_ADDRESSES'

    DBMS_REFRESH.MAKE(name => 'SG_ADDR_TABS',
                      tab => vSnapShotList,
                      next_date => TRUNC(sysdate) + 1,
                      interval => 'SYSDATE + 1',
                      implicit_destroy => TRUE,
                      lax => TRUE,
                      rollback_segment 'RB1');
END;

This example creates the same snapshot group as in the previous example, but with some additional properties:

implicit_destroy = TRUE: This setting causes the snapshot group SG_ADDR_TABS to be destroyed if all of the snapshots in the group are dropped. The default behavior is to preserve the snapshot group, even if it has no members.
lax = TRUE: If any of the snapshots being added to SG_ADDR_TABS exist in another snapshot group, this setting instructs Oracle to remove them from the other group before adding them to the new group. A snapshot cannot be a member of more than one snapshot group.
rollback_segment = `RB1': This setting causes Oracle to use rollback segment RB1 whenever it refreshes snapshot group SG_ADDR_TABS. You should consider specifying rollback segments if your snapshot refreshes result in long transactions requiring a large rollback segment.

14.2.2.1.4 Parallel propagation

In the next example, we create a snapshot refresh group that uses parallel propagation (Oracle8 only):

DECLARE
vSnapshotList dbms_utility.uncl_array
BEGIN
    vSnapshotList(1) = 'COUNTRIES'
    vSnapshotList(2) = 'STATES'
    vSnapshotList(3) = 'POSTAL_CODES'
    vSnapshotList(4) = 'CUSTOMER_ADDRESSES'

    DBMS_REFRESH.MAKE(name => 'SG_ADDR_TABS',
                      tab => vSnapShotList,
                      next_date => TRUNC(sysdate) + 1,
                      interval => 'SYSDATE + 1',
                      parallelism => 4,);
END;

This example sets parallelism to 4, so that Oracle uses four processes to perform the refresh.

14.2.2.2 The DBMS_REFRESH.DESTROY procedure

Call the DESTROY procedure to destroy a snapshot group. For both Oracle7 and Oracle8, you call DESTROY as follows,

PROCEDURE DBMS_REFRESH.DESTROY (name IN VARCHAR2);

where name is the name of the snapshot group to be destroyed.

The DESTROY procedure raises the following exception:

Name	Number	Description
ORA-23404	-23404	Refresh group name does not exist

14.2.2.2.1 Example

This example destroys the snapshot group SG_ADDR_TABS:

BEGIN
	DBMS_REFRESH.DESTROY( name => 'SG_ADDR_TABS' );
END;

This example does not drop the member snapshots themselves; however, they will not be refreshed again unless you either add them to another snapshot group, or refresh them manually with the DBMS_SNAPSHOT.REFRESH procedure.

14.2.3 Adding and Subtracting Snapshots from Snapshot Groups

With the ADD and SUBTRACT procedures, you can add and subtract the snapshots in a snapshot group after you have created the group. As with the other DBMS_REFRESH procedures, you must call these procedures from the snapshot site.

NOTE: A snapshot group cannot have more than 100 members.

14.2.3.1 The DBMS_REFRESH.ADD procedure

Call the ADD procedure to add a snapshot group. The specification follows:

PROCEDURE DBMS_REFRESH.ADD
   (name IN VARCHAR2,
   {list IN VARCHAR2,| tab IN dbms_utility.uncl_array,}
    lax IN BOOLEAN  DEFAULT FALSE );

The parameters for the ADD procedure have the same meaning as in the MAKE procedure; refer to the parameter table in that section. Note that you must select the list or tab parameter, but not both.

The ADD procedure does not raise any exceptions.

14.2.3.1.1 Example

This example uses the ADD procedure to add the snapshots PROVINCES and CONTINENTS to the existing snapshot group SG_ARR_TABS:

BEGIN
	DBMS_REFRESH.ADD
         (name => 'SG_ADDR_TABS', list => 'PROVINCES', CONTINENTS');
END;

14.2.3.2 The DBMS_REFRESH. SUBTRACT procedure

Call the SUBTRACT procedure to subtract a snapshot group. The specification follows:

PROCEDURE DBMS_REFRESH.SUBTRACT
    (name IN VARCHAR2,
    {list IN VARCHAR2,| tab IN dbms_utility.uncl_array,}
     lax IN BOOLEAN  DEFAULT FALSE );

The parameters for the SUBTRACT procedure have the same meaning as in the MAKE procedure; refer to the parameter table in that section. Note that you must select the list or tab parameter, but not both.

The SUBTRACT procedure does not raise any exceptions.

14.2.3.2.1 Example

The following example removes the snapshots STATES and COUNTRIES from the existing snapshot group SG_ADDR_TABS. Since we also specified lax = TRUE, the call also drops the snapshot group if there are no other member snapshots remaining.

BEGIN
    DBMS_REFRESH.SUBTRACT( name => 'SG_ADDR_TABS',
                           list => 'STATES', COUNTRIES',
                           lax => TRUE);
END;

14.2.4 Altering Properties of a Snapshot Group

The CHANGE procedure allows you to modify settings associated with a snapshot group. You can change any of the parameters that are available in DBMS_REFRESH.MAKE:

interval

implicit_destroy

rollback_segment

push_deferred_rpc

refresh_after_errors

purge_option (Oracle8 only)

parallelism (Oracle8 only)

heap_size (Oracle8 only)

Refer to the MAKE section for an explanation of these parameters.

14.2.4.1 The DBMS_REFRESH.CHANGE procedure

Call the CHANGE procedure to modify a snapshot group's setting. The specifications for CHANGE differ for Oracle7 and Oracle8 as follows.

Here is the Oracle7 specification:

PROCEDURE DBMS_REFRESH.CHANGE
   (name IN VARCHAR2,
    next_date IN DATE DEFAULT NULL,
    interval IN VARCHAR2 DEFAULT NULL,
    implicit_destroy IN BOOLEAN DEFAULT NULL,
    rollback_seg IN VARCHAR2 DEFAULT NULL,
    push_deferred_rpc IN BOOLEAN DEFAULT NULL,
    refresh_after_errors IN BOOLEAN DEFAULT NULL);

Here is the Oracle8 specification:

PROCEDURE DBMS_REFRESH.CHANGE
   (name IN VARCHAR2,
    next_date IN DATE := NULL,
    interval IN VARCHAR2 := NULL,
    implicit_destroy IN BOOLEAN := NULL,
    rollback_seg IN VARCHAR2 := NULL,
    push_deferred_rpc IN BOOLEAN := NULL,
    refresh_after_errors IN BOOLEAN := NULL,
    purge_option IN BINARY_INTEGER := NULL,
    parallelism IN BINARY_INTEGER := NULL,
    heap_size IN BINARY_INTEGER := NULL);

As with the MAKE procedure, the difference between the Oracle7 and Oracle8 CHANGE specifications is the inclusion of support for parallel propagation and purging in the Oracle8 version.

The CHANGE procedure does not raise any exceptions

14.2.5 Manually Refreshing Snapshot Groups

The REFRESH procedure refreshes a snapshot group.

14.2.5.1 The DBMS_REFRESH.REFRESH procedure

Call REFRESH to refresh a snapshot group. A call to REFRESH causes all members of snapshot group name to be refreshed with the settings that you have designated in DBMS_REFRESH.MAKE and/or DBMS_REFRESH.CHANGE. The specification is,

PROCEDURE DBMS_REFRESH.REFRESH (name IN VARCHAR2);

where name identifies the snapshot group.

The REFRESH procedure does not raise any exceptions.