Oracle provides a number of packages that let you perform various types of administrative operations on snapshots and snapshot logs. Most of these administrative operations are relevant only if you are using snapshot groups or the Oracle advanced replication option. This chapter describes the following packages:
Even if you are using PL/SQL's built-in snapshot packages, you will continue to use the CREATE SNAPSHOT command to create your snapshots.
The DBMS_SNAPSHOT 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, Introduction ) 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_SNAPSHOT for the package and grants EXECUTE privilege on the package to public. All Oracle users can reference and make use of this package.
DBMS_SNAPSHOT does not define any exceptions.
14.1.2 Using the I_AM_A_REFRESH Package State Variable
The I_AM_A_REFRESH and SET_I_AM_A_REFRESH programs query and set Oracle's REP$I_AM_A_REFRESH package variable. Oracle uses this variable in replication triggers and elsewhere internally to determine whether a given DML statement should be replicated to other master sites.
The I_AM_A_REFRESH function queries the REP$I_AM_A_REFRESH package variable. If this variable equals "Y," then the session is refreshing a snapshot or applying propagated DML to a replicated table. The header for the function follows:
FUNCTION DBMS_SNAPSHOT.I_AM_A_REFRESH RETURN BOOLEAN;
The function does not raise any exceptions.
Let's look at several examples of querying the I_AM_A_REFRESH package variable.
Suppose now that you are replicating a table named COUNTRIES in the SPROCKET schema:
SQL> DESC sprocket.countries Name Null? Type --------------- --------- -------------- COUNTRY_ID NOT NULL NUMBER(6) ISO3166_NUMBER NOT NULL NUMBER(3) ISO3166_NAME NOT NULL VARCHAR2(50) ISO2_CODE NOT NULL VARCHAR2(2) ISO3_CODE NOT NULL VARCHAR2(3) AUDIT_DATE NOT NULL DATE AUDIT_USER NOT NULL VARCHAR2(30) GLOBAL_NAME NOT NULL VARCHAR2(20)
When you generate replication support for this table with DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (described in Chapter 15 ), Oracle creates an AFTER ROW trigger named COUNTRIES$RT, which queues DML to other master sites. The text of the trigger follows:
after delete or insert or update on "SPROCKET"."COUNTRIES" for each row declare flag char; begin if "COUNTRIES$TP".active then if inserting then flag := 'I'; elsif updating then flag := 'U'; elsif deleting then flag := 'D'; end if; "COUNTRIES$TP".replicate( :old."AUDIT_DATE",:new."AUDIT_DATE", :old."AUDIT_USER",:new."AUDIT_USER", :old."COUNTRY_ID",:new."COUNTRY_ID", :old."GLOBAL_NAME",:new."GLOBAL_NAME", :old."ISO2_CODE",:new."ISO2_CODE", :old."ISO3166_NAME",:new."ISO3166_NAME", :old."ISO3166_NUMBER",:new."ISO3166_NUMBER", :old."ISO3_CODE",:new."ISO3_CODE", flag); end if; end;
As you can see, this trigger replicates DML only if the function COUNTRIES$TP.active is TRUE. This ACTIVE function uses DBMS_SNAPSHOT.I_AM_A_REFRESH as follows:
function active return boolean is begin return (not((is_snapshot and dbms_snapshot.I_am_a_refresh) or not dbms_reputil.replication_is_on)); end active;
Oracle uses the active function, which calls DBMS_SNAPSHOT.I_AM_A_REFRESH, to distinguish between your application's DML operations and the DML that is being propagated from another master site.
The base table of an updateable snapshot has a trigger that also uses the I_AM_A_REFRESH function.
Under some circumstances, you may need to determine the source of DML statements. For example, you will notice that the countries table has a number of fields used for auditing: audit_date, audit_user, and global_name. We have a BEFORE ROW trigger that populates these fields.
CREATE OR REPLACE TRIGGER countries_audit BEFORE INSERT OR UPDATE ON countries FOR EACH ROW DECLARE vGlobalName VARCHAR2(30) := DBMS_REPUTIL.GLOBAL_NAME; BEGIN IF NOT (DBMS_SNAPSHOT.I_AM_A_REFRESH) THEN BEGIN :new.audit_date := SYSDATE; :new.audit_user := USER; :new.global_name := vGlobalName; END IF; END;
This trigger fires when an application performs an INSERT or UPDATE, but not when the DML is propagated from other sites.
PROCEDURE DBMS_SNAPSHOT.SET_I_AM_A_REFRESH (value IN BOOLEAN);
where value is the value (Y or N) being set. This procedure does not raise any exceptions.
If you need to enable and disable replication triggers at the session level, you can do so with the SET_I_AM_A_REFRESH procedure. To enable the triggers, specify the following:
DBMS_SNAPSHOT.SET_I_AM_A_REFRESH( value => FALSE )
To disable them, specify the following:
DBMS_SNAPSHOT.SET_I_AM_A_REFRESH( value => TRUE )
Use this package carefully, because disabling replication triggers effectively disables any conflict resolution mechanisms you may have defined. (See Chapter 17, Deferred Transactions and Remote Procedure Calls , for a discussion of these mechanisms.)
Calling the REFRESH procedure from a snapshot site forces the refresh of the specified snapshot(s). Typically, this procedure is used to refresh an individual snapshot, or a group of snapshots that are not in the same snapshot refresh group.
Call the REFRESH procedure to force a snapshot refresh. The specifications for the Oracle7 and Oracle8 versions of the REFRESH procedure differ. Note that the Version 8.0 implementation adds parameters that support parallelism, and drops the execute_as_user parameter. Both versions are overloaded, allowing you to specify the list of snapshots as a comma-delimited string in the list parameter, or as a PL/SQL table in the tab parameter. The other parameters are identical for the two versions.
Here is the Oracle7 specification:
PROCEDURE DBMS_SNAPSHOT.REFRESH (list IN VARCHAR2, method IN VARCHAR2 DEFAULT NULL, rollback_seg IN VARCHAR2 DEFAULT NULL, push_deferred_rpc IN BOOLEAN DEFAULT TRUE, refresh_after_errors IN BOOLEAN DEFAULT FALSE, execute_as_user IN BOOLEAN DEFAULT FALSE ); PROCEDURE DBMS_SNAPSHOT.REFRESH (tab IN OUT dbms_utility.uncl_array, method IN VARCHAR2 DEFAULT NULL, rollback_seg IN VARCHAR2 DEFAULT NULL, push_deferred_rpc IN BOOLEAN DEFAULT TRUE, refresh_after_errors IN BOOLEAN DEFAULT FALSE, execute_as_user IN BOOLEAN DEFAULT FALSE );
Here is the Oracle8 specification:
PROCEDURE DBMS_SNAPSHOT.REFRESH (list IN VARCHAR2, method IN VARCHAR2 := NULL, 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); PROCEDURE DBMS_SNAPSHOT.REFRESH (tab IN OUT dbms_utility.uncl_array, method IN VARCHAR2 := NULL, 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);
Parameters are summarized in the following table.
The REFRESH procedure does not raise any exceptions.
All of the snapshots passed to list or tab are refreshed as a single transaction; all or none are refreshed. In addition, the refreshed snapshots will respect all integrity constraints that exist among the master tables.
You might want to force a manual refresh of a snapshot if the next scheduled refresh is too far in the future, or if you have repaired a problem that caused the scheduled refresh job to break. Forcing a manual refresh of a snapshot does not alter its refresh schedule.
A FAST refresh requires a snapshot log on the master table, and is possible only for simple snapshots in Oracle7; Oracle8 supports fast refreshes subquery snapshots meeting certain conditions. Fast refreshes read the snapshot log to determine which rows have changed since the last refresh, and only those rows are updated.
If you are concerned about the amount of rollback the refresh will require, you can use the rollback_seg parameter to designate a rollback segment that is suitably sized for the transaction. However, you are not guaranteed that no other transactions will use this rollback segment. In general, you should consider making relatively large rollback segments if you anticipate frequent refreshes of large snapshots.
You can call REFRESH only from a snapshot site.
Once you are familiar with the various parameters to the REFRESH procedure, it becomes simple to use, as the following examples illustrate.
This example shows a refresh as a read-only snapshot named PRICE_LIST:
BEGIN DBMS_SNAPSHOT.REFRESH (list => 'PRICES'); END;
This is the simplest possible refresh method. Note that since we have not provided a schema name, this would have to be executed from the snapshot owner's account.
In the next example, we refresh a set of related 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_SNAPSHOT.REFRESH( tab => vSnapShotList, method => 'CCF?', rollback_segment => 'RB1' execute_as_user => FALSE); END;
This example illustrates several points:
In the next example, we refresh the updateable snapshot DAILY_STORE_SALES.
DECLARE vSnapshotList dbms_utility.uncl_array BEGIN vSnapshotList(1) = 'DAILY_STORE_SALES' DBMS_SNAPSHOT.REFRESH( tab => vSnapShotList, method => '?' push_deferred_rpc => FALSE); END;
Since we set push_deferred_rpc to FALSE (the default is TRUE), the refresh will overwrite any local changes. The local changes will be visible again after the remote procedure call (RPC) pushes them to the master site and snapshot is refreshed again.
In this example, the parallelism feature of Oracle8 allows us to use four processes to refresh the updateable snapshot DAILY_STORE_SALES:
DECLARE vSnapshotList dbms_utility.uncl_array BEGIN vSnapshotList(1) = 'DAILY_STORE_SALES' DBMS_SNAPSHOT.REFRESH( tab => vSnapShotList, method => '?' parallelism => 4, purge_option = 2); END;
The purge_option parameter controls how Oracle purges the snapshot site's deferred transaction queue; Oracle8 does not purge the queue automatically when the transactions propagate, so you must use DBMS_DEFER_SYS.SCHEDULE_PURGE (described in Chapter 17 ) to schedule a job to purge the queue, lest it become large and unmanageable. The purge_option parameter in REFRESH provides an opportunity to purge the queue of transactions associated with the updateable snapshot(s) you are refreshing.
The PURGE_LOG procedure deletes records from the snapshot log on a master table. You may wish to do this if the snapshot log becomes very large, or if you drop a subset of the snapshots for which the table is a master.
Call the PURGE_LOG procedure to delete snapshot log records. The specification for the PURGE_LOG procedure follows:
PROCEDURE DBMS_SNAPSHOT.PURGE_LOG (master VARCHAR2 ,num BINARY_INTEGER DEFAULT 1 ,flag VARCHAR2 DEFAULT 'NOP' );
Parameters are summarized in the following table.
The PURGE_LOG procedure does not raise any exceptions.
The following examples illustrate the use of the PURGE_LOG procedure. The first example shows the simplest form of the procedure; the only parameter is master:
BEGIN DBMS_REFRESH.PURGE_LOG( master => 'COUNTRIES' ); END;
Since the call uses the default value for num, 1, it will purge the snapshot log records required for a fast refresh of the least recently refreshed snapshot -- that is, the most stale snapshot.
This example shows the use of several parameters:
BEGIN DBMS_REFRESH.PURGE_LOG ( master => 'COUNTRIES', num => 5, flag => 'DELETE' ); END;
In this example, the procedure deletes snapshot log records required for a fast refresh of the five most stale snapshots. However, since we have set the flag parameter to DELETE, the call is guaranteed to delete the records for at least one snapshot, even if the table masters fewer than five snapshots.
Occasionally a DBA must reorganize a table -- in other words, coalesce its extents and reduce row chaining. Two new programs in Oracle8 allow you to reorganize a master table without invalidating its snapshot log: BEGIN_TABLE_REORGANIZATION and END_TABLE_REORGANIZATION. Therefore, you do not have to perform complete refreshes of the table's snapshots after it is reorganized. To take advantage of this new feature, you must be using primary key snapshots.
If you are reorganizing a table, call the BEGIN_TABLE_REORGANIZATION procedure before reorganizing the table, and the END_TABLE_REORGANIZATION procedure when you are finished. The specification for BEGIN_TABLE_REORGANIZATION follows:
PROCEDURE DBMS_SNAPSHOT.BEGIN_TABLE_REORGANIZATION (tabowner IN VARCHAR2 ,tabname IN VARCHAR2);
Parameters are summarized in the following table.
This procedure does not raise any exceptions.
PROCEDURE DBMS_SNAPSHOT.END_TABLE_REORGANIZATION (tabowner IN VARCHAR2
Parameters are the same as those for BEGIN_TABLE_REORGANIZATION. This procedure does not raise any exceptions.
The following examples illustrate how to use these procedures as part of a table reorganization. The first example shows the steps for reorganizing a master table using truncation.
The next example shows the steps for reorganizing a master table using RENAME TABLE.
One of the most significant improvements in Oracle8 is the automatic registration of snapshots at the master site. In Oracle7, there was no easy way to determine the location -- or even the existence -- of snapshots with master table(s) in your instance. But when you create a snapshot in Oracle8, Oracle puts a record in the DBA_REGISTERED_SNAPSHOTS data dictionary view. Similarly, when you drop a snapshot, Oracle deletes the record from DBA_REGISTERED_SNAPSHOTS.
The REGISTER and UNREGISTER procedures let you manually maintain this data dictionary view, shown in Table Table 14.2 .
188.8.131.52 The DBMS_SNAPSHOT. REGISTER_SNAPSHOT procedure (Oracle 8 only)
Generally, the registration and unregistration of snapshots is automatic if both the master and snapshot databases are Oracle8. However, in case the snapshot site is running Oracle7, or if the automatic registration fails, you can use the Oracle8 procedure, REGISTER_SNAPSHOT, to register the snapshot manually.
The specification for the REGISTER_SNAPSHOT procedure is as follows:
PROCEDURE DBMS_SNAPSHOT.REGISTER_SNAPSHOT (snapowner IN VARCHAR2, snapname IN VARCHAR2, snapsite IN VARCHAR2, snapshot_id IN DATE | BINARY_INTEGER, flag IN BINARY_INTEGER, qry_txt IN VARCHAR2, rep_type IN BINARY_INTEGER := dbms_snapshot.reg_unknown);
The REGISTER_SNAPSHOT procedure is overloaded; snapshot_id is a DATE type if the snapshot site is an Oracle7 database, and BINARY_INTEGER if it is an Oracle8 database. Parameters are summarized in the following table.
REGISTER_SNAPSHOT does not raise any exceptions.
Registration of Oracle7 snapshots is never automatic; you must call REGISTER_SNAPSHOT if you want to see Oracle7 snapshots in the DBA_REGISTERED_SNAPSHOTS data dictionary view. You must provide all of the information you want to see. In the following example, you register a snapshot from an Oracle7 snapshot site to an Oracle8 master:
BEGIN DBMS_SNAPSHOT.REGISTER_SNAPSHOT( snapowner => 'SPROCKET', snapname => 'COUNTRIES', snapsite => 'D7CA.BIGWHEEL.COM', snapshot_id => sysdate, flag => 0, qry_text => 'SELECT * FROM countries@D8CA.BIGWHEEL.COM', rep_typ e => reg_v7_group); END;
The UNREGISTER_SNAPSHOT procedure is the flip side of the REGISTER_SNAPSHOT procedure. You use UNREGISTER_SNAPSHOT when you need to manually unregister a snapshot. This procedure unregisters snapshots at the master site, regardless of whether they were registered manually or automatically.
The specification is as follows:
PROCEDURE DBMS_SNAPSHOT.UNREGISTER_SNAPSHOT (snapowner IN VARCHAR, snapname IN VARCHAR2, snapsite IN VARCHAR2)
See the description of parameters under the REGISTER procedure.
UNREGISTER_SNAPSHOT does not raise any exceptions.
In this example, we unregister the snapshot that we created in the previous section:
BEGIN DBMS_SNAPSHOT.UNREGISTER_SNAPSHOT( snapowner => 'SPROCKET', snapname => 'COUNTRIES', snapsite => 'D7CA.BIGWHEEL.COM'); END
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.