Oracle7 Server Distributed Systems Volume II: Replicated Data

Managing Snapshot Logs

A snapshot log is a table, in the same database as the master table for a snapshot, that is associated with the master table. Its rows list changes that have been made to the master table, and information about which snapshots have and have not been updated to reflect those changes. You can create a snapshot log to decrease the amount of processing and time needed to refresh the simple snapshot.

Attention: Snapshot logs cannot be used with complex snapshots.

A snapshot log is associated with a single master table; likewise, a master table can have only one snapshot log. If multiple simple snapshots are based on the same master table, they all use the same snapshot log.

These sections explain how to create, manage, and drop snapshot logs.

Creation Order of a Simple Snapshot and the Snapshot Log

If you are creating a simple snapshot, it is more efficient to create the snapshot log before the snapshot. Figure 3 - 3 illustrates the two orders of creation.

Figure 3 - 3. Creation Order of a Simple Snapshot and the Snapshot Log

In Method A, the first refresh of the snapshot cannot use the log because the log cannot reflect all updates entered between the creation of the snapshot and the creation of the snapshot log; therefore, two complete refreshes are necessary.

In contrast, Method B only requires one complete refresh (when creating the snapshot); subsequent refreshes can immediately use the snapshot log. If the master table is large or a number of simple snapshots are based on the same master table, creating the snapshot log before the snapshots can be much more efficient.

Creating a Snapshot Log

Create a snapshot log in the same database as the master table using the SQL command CREATE SNAPSHOT LOG. You can set storage options for the snapshot log's data blocks, extent sizes and allocation, and tablespace to hold the snapshot log. For example, the following statement creates a snapshot log associated with the EMP table:

CREATE SNAPSHOT LOG ON scott.emp
    TABLESPACE users
    STORAGE (INITIAL 10K NEXT 10K PCTINCREASE 50)
    PCTFREE 5;

Naming Snapshot Logs

Oracle automatically creates the snapshot log in the schema that contains the master table. Since you cannot specify a name for the snapshot log (one is implicitly given by Oracle), uniqueness is not a concern.

The Internals of Snapshot Log Creation

When you create a snapshot log, Oracle performs several operations internally:

Oracle creates a table, named MLOG$_master_table_name, to store the ROWID and timestamp of rows updated in the master table. The timestamp column is not updated until the log is first used by a snapshot refresh.

Oracle creates an AFTER ROW trigger on the master table to insert the ROWIDs and timestamps of inserted, updated, and deleted rows into the master snapshot log. The trigger is named TLOG$_master_table_name.

Additional Information: Refer to Oracle7 Server Application Developer's Guide to learn more about triggers.

The underlying table for a snapshot log and associated trigger are contained in the same schema as the master table. For both the table and the log, the master_table_name is truncated at 20 bytes (if necessary) and appended with a four-digit number to ensure uniqueness. This guarantees that the objects comply with the naming rules for schema objects.

Attention: Do not alter or change data in these objects.

Privileges Required to Create Snapshot Logs

If you own the master table, you can create an associated snapshot log if you have the CREATE TABLE and CREATE TRIGGER system privileges. If you are creating a snapshot log for a table in another user's schema, you must have the CREATE ANY TABLE and CREATE ANY TRIGGER system privileges. In either case, the owner of the snapshot log must have sufficient quota in the tablespace intended to hold the snapshot log.

The privileges required to create a snapshot log directly relate to the privileges necessary to create the underlying objects associated with a snapshot log.

Setting Storage Parameters for Snapshot Logs

Set a snapshot log's storage options as follows:

Set PCTFREE to 0, and PCTUSED to 100.

Set extent storage parameters according to the update activity (number of INSERT, UPDATE, and DELETE statements) on the master table.

Each row in a snapshot log takes approximately 26 bytes (18 bytes for an Oracle ROWID, seven bytes for a timestamp, and one byte for DML type). Use this number to calculate how much space a snapshot log requires, using the procedure described for calculating space required by non-clustered tables in the Oracle7 Server Administrator's Guide.

Altering Snapshot Log Storage Parameters

You can alter a snapshot log's storage parameters using the SQL command ALTER SNAPSHOT LOG. For example:

ALTER SNAPSHOT LOG sale_price

   PCTFREE 25

   PCTUSED 40;

Privileges Required to Alter Snapshot Logs Only the owner of the master table, or a user with the ALTER ANY TABLE system privilege can alter the storage parameters of a snapshot log.

Managing Snapshot Log Space Use

Oracle automatically tracks which rows in a snapshot log have been used during the refreshes of snapshots, and purges these rows from the log so that the log does not grow endlessly. Because multiple simple snapshots can use the same snapshot log, rows used in the refresh of one snapshot may still be needed to refresh another snapshot; Oracle does not delete rows from the log until all snapshots have used them. However, this automated feature can cause a snapshot log to grow indefinitely if a single associated snapshot is never refreshed.

For example, Snapshot EMP_B is regularly refreshed. However, Oracle cannot purge the rows used during the refresh of Snapshot EMP_B because Snapshot EMP_A needs them for its next refresh. This situation occurs when you have several simple snapshots based on the same master table and

One snapshot is not configured to be automatically refreshed by Oracle; it has to be manually refreshed.

One snapshot has a large refresh interval, such as every year.

A network failure has prevented an automatic refresh of one or more of the snapshots based on the master table.

A network or site failure has prevented a dropped snapshot from unregistering itself from its master.

Purging the Snapshot Log

Keep the snapshot log as small as possible to minimize the space it uses. To reduce the number of rows in a snapshot log, you can either refresh the snapshots associated with the log, or shrink the log by deleting the rows only required by the Nth least recently refreshed snapshots. To do the latter, execute the PURGE_LOG stored procedure of the DBMS_SNAPSHOT package. For example, to purge the log entries in the example above that are needed only for snapshot EMP_A, the least recently refreshed snapshot, you could execute the following procedure:

DBMS_SNAPSHOT.PURGE_LOG( master => emp,
                         num    => 1);

Additional Information: The parameters for the PURGE_LOG procedure are described in Table 12 - 187.

Reducing Space Allocation for a Snapshot Log

If a snapshot log grows and allocates many extents, purging the log of rows does not reduce the amount of space allocated for the log. To reduce the space allocated for a snapshot log, first copy the rows in the snapshot log to a new location. Then truncate the log and reinsert the old rows. This avoids having to perform a complete refresh of the dependent snapshots. However, any changes made to the master table between the time that you copy the rows to a new location and when you truncate the log will be lost, until the next complete refresh.

Privileges Required to Delete Rows from a Snapshot Log The owner of a snapshot log or a user with the DELETE ANY TABLE system privilege can purge rows from the snapshot log by executing the PURGE_LOG procedure.

Dropping Snapshot Logs

You can drop a snapshot log independently of its master table or any existing snapshots. You might decide to drop a snapshot log if one of the following is true:

All simple snapshots of a master table have been dropped.

All simple snapshots of a master table are to be completely refreshed, not fast refreshed.

To drop a local snapshot log, use the SQL command DROP SNAPSHOT LOG, as in

DROP SNAPSHOT LOG emp_log;

Privileges Required to Drop a Snapshot Log

Only the owner of the master table, or a user with the DROP ANY TABLE system privilege can drop a snapshot log.