Oracle7 Server SQL Reference

CREATE SNAPSHOT LOG

Purpose

To create a snapshot log. A snapshot log is a table associated with the master table of a snapshot. Oracle7 stores changes to the master table's data in the snapshot log and then uses the snapshot log to refresh the master table's snapshots.

Prerequisites

The privileges required to create a snapshot log directly relate to the privileges necessary to create the underlying objects associated with a snapshot log. For example, you must have the privileges necessary to create a table in the schema of the master table. For information on these privileges, see the CREATE TABLE command

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.

Before a snapshot log can be created, the user SYS must run the SQL scripts DBMSSNAP.SQL and PRVTSNAP.PLB on the database containing the master table.. If you have the procedural option, this is done automatically This script creates the package DBMS_SNAPSHOT, which contains the stored procedures used for refreshing the snapshot and for purging the snapshot log. The exact name and location of this script may vary depending on your operating system.

You must also have the privileges to create a trigger on the master table. For information on these privileges, see the CREATE TRIGGER command .

To create a snapshot log, you must be using Oracle7 with PL/SQL installed.

If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must dominate the label of the tablespace in which the snapshot log is to be stored.

Syntax

Keywords and Parameters

schema

is the schema containing the snapshot log's master table. If you omit schema, Oracle7 assumes the master table is contained in your own schema. Oracle7 creates the snapshot log in the schema of its master table. You cannot create a snapshot log for a table in the schema of the user SYS.

table

is the name of the master table for which the snapshot log is to be created. You cannot create a snapshot log for a view.

Oracle7 chooses names for the table and trigger used to maintain the snapshot log by prefixing and suffixing the master table name. To limit these names to 30 bytes and allow them to contain the entire master table name, It is recommended that you limit master table names to 20 bytes.

PCTFREE PCTUSED INITRANS MAXTRANS

establishes values for the specified parameters for the snapshot log. See the descriptions of these parameters in the CREATE TABLE command .

TABLESPACE

specifies the tablespace in which the snapshot log is to be created. If you omit this option, Oracle7 creates the snapshot log in the default tablespace the owner of the snapshot log's schema.

STORAGE

establishes storage characteristics for the snapshot log. See the STORAGE clause .

Usage Notes

If you are using Trusted Oracle7, the new snapshot log is automatically labeled with your DBMS label.

Using Snapshot Logs

A snapshot log is a table that is associated with the master table of a snapshot . When changes are made to the master table's data, Oracle7 adds rows describing these changes to the snapshot log. Later Oracle7 can use these rows to refresh snapshots based on the master table. This process is called a fast refresh. Without a snapshot log, Oracle7 must execute the snapshot query to refresh the snapshot. This process is called a complete refresh. Usually, a fast refresh takes less time than a complete refresh.

A snapshot log is located in the master database in the same schema as the master table. You can create only a single snapshot log for a master table. Oracle7 can use this snapshot log to perform fast refreshes for all simple snapshots based on the master table. Oracle7 records changes in the snapshot log only if there is a simple snapshot based on the master table. For more information on snapshots, including how Oracle7 refreshes snapshots, see the CREATE SNAPSHOT command and Oracle7 Server Distributed Systems, Volume II.

Example

The following statement creates a snapshot log on the employee table:

CREATE SNAPSHOT LOG ON emp

	PCTFREE 5

	TABLESPACE users

	STORAGE (INITIAL 10K NEXT 10K PCTINCREASE 50)

Oracle7 can use this snapshot log to perform a fast refresh on any simple snapshot subsequently created on the EMP table.