14.1.2.1.1 Examples

Let's look at several examples of querying the I_AM_A_REFRESH package variable.

14.1.2.1.2 Generating replication support

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.

14.1.2.1.3 Auditing triggers

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.

NOTE: All row-level replication triggers are AFTER ROW triggers. Although a table can have multiple triggers of the same type, you cannot control the order in which they are fired. Therefore, it is safest to use BEFORE ROW triggers to perform auditing on replicated tables; in this way, you are guaranteed that BEFORE ROW triggers fire before AFTER ROW triggers.

14.1.2.2.1 Example

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.)

14.1.3.1.1 Restrictions

You can call REFRESH only from a snapshot site.

14.1.3.1.2 Examples

Once you are familiar with the various parameters to the REFRESH procedure, it becomes simple to use, as the following examples illustrate.

14.1.3.1.3 Read-only snapshot

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.

14.1.3.1.4 Related read-only snapshots

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:

1. You can provide the list of snapshots as a PL/SQL table. Oracle will refresh all of the snapshots in one atomic transaction; either all or none of the snapshots are refreshed. All referential consistencies among the master tables will be preserved in the snapshot tables.

2. You can specify different refresh methods for each snapshot. This example performs a complete refresh on COUNTRIES and STATES, a full refresh on POSTAL_CODES, and a fast refresh (if possible) on CUSTOMER_ADDRESSES. If Oracle cannot use a fast refresh on the CUSTOMER_ADDRESS table, it will perform a complete refresh instead.

3. You can designate a specific, suitably sized rollback segment for the refresh.

4. You can set the parameter, execute_as_user, to FALSE to force Oracle to refresh the snapshot under the privilege domain of the snapshot owner.

14.1.3.1.5 Updateable snapshot.

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.

14.1.3.1.6 Parallel refreshes

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.

NOTE: Purging the deferred transaction queue is not the same thing as purging a snapshot log!

14.1.4.1.1 Examples

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.

NOTE: To delete all records from a snapshot log, set the num parameter to a high value (greater than the number of snapshots mastered to the master table, specified in the master parameter).

14.1.5.2.1 Examples

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.

1. Call DBMS_SNAPSHOT.BEGIN_TABLE_REORGANIZATION:

   BEGIN
   	EXECUTE DBMS_SNAPSHOT.BEGIN_TABLE_REORGANIZATION (
   		tabowner => 'SPROCKET', tabname => 'COUNTRIES');
   END;

2. Back up the table by exporting it, or spooling it to a flat file.

3. Truncate the master table, preserving the snapshot log:

   TRUNCATE TABLE countries PRESERVE SNAPSHOT LOG;

4. Restore the table from the export file or flat file.

5. Call DBMS_SNAPSHOT.END_TABLE_REORGANIZATION:

   BEGIN
   	EXECUTE DBMS_SNAPSHOT.END_TABLE_REORGANIZATION (
   		tabowner => 'SPROCKET', tabname => 'COUNTRIES');
   END;

The next example shows the steps for reorganizing a master table using RENAME TABLE.

1. Call DBMS_SNAPSHOT.BEGIN_TABLE_REORGANIZATION:

   BEGIN
   	EXECUTE DBMS_SNAPSHOT.BEGIN_TABLE_REORGANIZATION (
   		tabowner => 'SPROCKET', tabname => 'COUNTRIES');
   END;

2. Rename the table:

   RENAME TABLE countries TO countries_pre_reorg;

3. Create a new version of the table:

   CREATE TABLE countries AS SELECT * FROM countrie_pre_reorg;

4. Call DBMS_SNAPSHOT.END_TABLE_REORGANIZATION:

   BEGIN
   	EXECUTE DBMS_SNAPSHOT.END_TABLE_REORGANIZATION (
   		tabowner => 'SPROCKET', tabname => 'COUNTRIES');
   END;

5. Recreate any triggers that were defined on the table.

In both of these examples, snapshots will be able to use the snapshot log for fast refreshes after the table reorganization is complete.

14.1.6.1.1 Example

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 [email protected]',
		rep_typ	e	=> reg_v7_group);
END;

14.1.6.2.1 Example

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

For a detailed example, see the mastsnap.sql file on the companion disk. This script lists registered snapshots at a master site, including their last refresh time. It requires Oracle8.