|
Chapter 14 Snapshots |
|
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.
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.
|
Name |
Description |
Use in SQL? |
|---|---|---|
|
Adds one or more snapshots to an existing refresh group |
No |
|
|
Changes parameters associated with a refresh group |
No |
|
|
Removes a refresh group |
No |
|
|
Creates a refresh group |
No |
|
|
Forces a refresh of a refresh group |
No |
|
|
Removes one or more snapshots from a refresh group |
No |
The MAKE and DESTROY procedures create and destroy snapshot groups, respectively. You call these procedures from the snapshot site.
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.
|
|
parallelism (Oracle8 only) |
|
|
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.
The following examples illustrate how the MAKE procedure may be used.
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.
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:
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.
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.
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.
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.
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 |
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.
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.
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.
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;
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.
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;
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.
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 REFRESH procedure refreshes a snapshot group.
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.
|
|
|
|
| 14.1 DBMS_SNAPSHOT: Managing Snapshots |
|
14.3 DBMS_OFFLINE_SNAPSHOT: Performing Offline Snapshot Instantiation |
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.