ALTER SNAPSHOT

Purpose

• changing its storage characteristics

• changing its automatic refresh mode and times

Prerequisites

If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the snapshot's creation label or you must satisfy one of the following criteria:

• If the snapshot's creation label is higher than your DBMS label, you must have READUP and WRITEUP system privileges

• If the snapshot's creation label is lower than your DBMS label, you must have WRITEDOWN system privilege.

• If the snapshot's creation label and your DBMS label are not comparable, you must have READUP, WRITEUP, and WRITEDOWN system privileges.

Syntax

Keywords and Parameters

schema

is the schema containing the snapshot. If you omit schema, Oracle7 assumes the snapshot is in your own schema.

snapshot

is the name of the snapshot to be altered.

PCTFREE PCTUSED INITRANS MAXTRANS

change the values of these parameters for the internal table that Oracle7 uses to maintain the snapshot's data. For information on the PCTFREE, PCTUSED, INITRANS, and MAXTRANS parameters, see the CREATE TABLE command .

STORAGE

changes the storage characteristics of the internal table Oracle7 uses to maintain the snapshot's data. See the STORAGE clause .

USING INDEX

changes the value of INITRANS, MAXTRANS, and STORAGE parameters for the index Oracle7 uses to maintain the snapshot's data. If USING INDEX is not specified then the index is written to the user's default tablespace.

REFRESH

changes the mode and times for automatic refreshes:

FAST specifies a fast refresh, or a refresh using the snapshot log associated with the master table.

COMPLETE specifies a complete refresh, or a refresh that re-executes the snapshot's query.

FORCE specifies a fast refresh if one is possible or complete refresh if a fast refresh is not possible. Oracle7 decides whether a fast refresh is possible at refresh time.

If you omit the FAST, COMPLETE, and FORCE options, Oracle7 uses FORCE by default.

START WITH specifies a date expression for the next automatic refresh time.

NEXT specifies a new date expression for calculating the interval between automatic refreshes.

START WITH and NEXT values must evaluate to times in the future.

Usage Notes

Example I

The following statement changes the automatic refresh mode for the HQ_EMP snapshot to FAST:

ALTER SNAPSHOT hq_emp
 	REFRESH FAST 

The next automatic refresh of the snapshot will be a fast refresh provided it is a simple snapshot and its master table has a snapshot log that was created before the snapshot was created or last refreshed.

Because the REFRESH clause does not specify START WITH or NEXT values, the refresh intervals established by the REFRESH clause when the HQ_EMP snapshot was created or last altered are still used.

Example II

The following statement stores a new interval between automatic refreshes for the BRANCH_EMP snapshot:

ALTER SNAPSHOT branch_emp

 	REFRESH NEXT SYSDATE+7 

Because the REFRESH clause does not specify a START WITH value, the next automatic refresh occurs at the time established by the START WITH and NEXT values specified when the BRANCH_EMP snapshot was created or last altered.

At the time of the next automatic refresh, Oracle7 refreshes the snapshot, evaluates the NEXT expression SYSDATE+7 to determine the next automatic refresh time, and continues to automatically refresh the snapshot once a week.

Because the REFRESH clause does not explicitly specify a refresh mode, Oracle7 continues to use the refresh mode specified by the REFRESH clause of a previous CREATE SNAPSHOT or ALTER SNAPSHOT statement.

Example III

The following statement specifies a new refresh mode, next refresh time, and new interval between automatic refreshes of the SF_EMP snapshot:

ALTER SNAPSHOT sf_emp
 	REFRESH COMPLETE   
	START WITH TRUNC(SYSDATE+1) + 9/24  
	NEXT SYSDATE+7 

The START WITH value establishes the next automatic refresh for the snapshot to be 9:00am tomorrow. At that point, Oracle7 performs a fast refresh of the snapshot, evaluates the NEXT expression, and subsequently refreshes the snapshot every week.

Related Topics