CREATE SNAPSHOT

Purpose

Prerequisites

• The distributed option must be installed.

• To create a snapshot in your own schema, you must have the CREATE SNAPSHOT, CREATE TABLE, and CREATE VIEW system privileges, and SELECT privilege on the master tables.

• To create a snapshot in another user's schema, you must have the CREATE ANY SNAPSHOT system privilege, as well as SELECT privilege on the master table. Additionally, the owner of the snapshot must be able to create the snapshot.

• To use updatable snapshots, the replication option must be installed and you must have the CREATE TRIGGER system privilege.

When you create a snapshot, Oracle7 creates a table, two views, and an index in the schema of the snapshot. Oracle7 uses these objects to maintain the snapshot's data. You must have the privileges necessary to create these objects. For information on these privileges, see the CREATE TABLE command , the CREATE VIEW command , and the CREATE INDEX command .

The owner of the schema containing the snapshot must have either space quota on the tablespace to contain the snapshot or UNLIMITED TABLESPACE system privilege. Also, both you (the creator) and the owner must also have the privileges necessary to issue the snapshot's query. For information on these privileges, see the SELECT command .

To create or refresh a snapshot, Oracle7 must be installed with PL/SQL. To create a snapshot on a remote table or view, Oracle7 must be installed with the distributed option.

Syntax

Keywords and Parameters

schema

is the schema to contain the snapshot. If you omit schema, Oracle7 creates the snapshot in your schema.

snapshot

is the name of the snapshot to be created.

Oracle7 chooses names for the table, views, and index used to maintain the snapshot by adding a prefix and suffix to the snapshot name. To limit these names to 30 bytes and allow them to contain the entire snapshot name, It is recommended that you limit your snapshot names to 19 bytes.

PCTFREE PCTUSED INITRANS MAXTRANS

establishes values for the specified parameters for the internal table Oracle7 uses to maintain the snapshot's data. For information on the PCTFREE, PCTUSED, INITRANS, and MAXTRANS parameters, see the CREATE TABLE command . For information on the STORAGE clause, see .

TABLESPACE

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

STORAGE

establishes storage characteristics for the table Oracle7 uses to maintain the snapshot's data.

CLUSTER

creates the snapshot as part of the specified cluster. Since a clustered snapshot uses the cluster's space allocation, do not use the PCTFREE, PCTUSED, INITRANS, MAXTRANS, TABLESPACE, or STORAGE parameters with the CLUSTER option.

USING INDEX

specifies parameters for the index Oracle7 creates to maintain the snapshot. You can choose the values of the INITRANS, MAXTRANS, TABLESPACE, STORAGE, and PCTFREE parameters for the index. For information on the PCTFREE, PCTUSED, INITRANS, and MAXTRANS parameters, see the CREATE TABLE command . For information on the STORAGE clause, see .

REFRESH

specifies how and when Oracle7 automatically refreshes the snapshot:

FAST specifies a fast refresh, or a refresh using only the updated data stored in 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 first automatic refresh time.

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

Both the START WITH and NEXT values must evaluate to a time in the future. If you omit the START WITH value, Oracle7 determines the first automatic refresh time by evaluating the NEXT expression when you create the snapshot. If you specify a START WITH value but omit the NEXT value, Oracle7 refreshes the snapshot only once. If you omit both the START WITH and NEXT values or if you omit the REFRESH clause entirely, Oracle7 does not automatically refresh the snapshot.

FOR UPDATE

Allows a simple snapshot to be updated. When used in conjunction with the Replication Option, these updates will be propagated to the master. For more information, see Oracle7 Server Distributed Systems, Volume II.

AS subquery

specifies the snapshot query. When you create the snapshot, Oracle7 executes this query and places the results in the snapshot. The select list can contain up to 253 expressions. For the syntax of a snapshot query, see the syntax description of subquery . The syntax of a snapshot query is subject to the same restrictions as a view query. For a list of these restrictions, see the CREATE VIEW command .

Usage Notes

Snapshots are useful in distributed databases. Snapshots allow you to maintain read-only copies of remote data on your local node. You can select data from a snapshot as if it were a table or view.

It is recommended that you qualify each table and view in the FROM clause of the snapshot query with the schema containing it.

Snapshots cannot contain long columns.

For more information on snapshots, see Oracle7 Server Distributed Systems, Volume II.

Types of Snapshots

simple

A simple snapshot is one in which the snapshot query selects rows from only one master table. This master table must be a table, not a view. Each row of a simple snapshot must be based on a single row of this table. The query for a simple snapshot cannot contain any of the following SQL constructs:

• GROUP BY clause

• CONNECT BY clause

• subqueries

• joins

• set operations

complex

A complex snapshot is one in which the snapshot query contains one or more of the constructs not allowed in the query of a simple snapshot. A complex snapshot can be based on multiple master tables on multiple master databases.

Refreshing Snapshots

After you create a snapshot, you can subsequently change its automatic refresh mode and time with the REFRESH clause of the ALTER SNAPSHOT command. You can also refresh a snapshot immediately with the DBMS_SNAPSHOT.REFRESH() procedure.

Specifying Refresh Modes

Fast To perform a fast refresh, Oracle7 updates the snapshot with the changes to the master table recorded in its snapshot log. For more information on snapshot logs, see the CREATE SNAPSHOT LOG command .

Oracle7 can only perform a fast refresh if all of the following conditions are true:

• The snapshot is a simple snapshot.

• The snapshot's master table has a snapshot log.

• The snapshot log was created before the snapshot was last refreshed or created.

Complete To perform a complete refresh, Oracle7 executes the snapshot query and places the results in the snapshot. If you specify a complete refresh, Oracle7 performs a complete refresh regardless of whether a fast refresh is possible.

A fast refresh is often faster than a complete refresh because it sends less data from the master database across the network to the snapshot's database. A fast refresh sends only changes to master table data, while a complete refresh sends the complete result of the snapshot query.

You can also use the FORCE option of the REFRESH clause to allow Oracle7 to decide how to refresh the snapshot at the scheduled refresh time. If a fast refresh is possible based on the fast refresh conditions, then Oracle7 performs a fast refresh. If a fast refresh is not possible, then Oracle7 performs a complete refresh.

Specifying Automatic Refresh Times

1. Specify the START WITH and NEXT parameters in the REFRESH clause of the CREATE SNAPSHOT statement. These parameters establish the time of the first automatic refresh time and the interval between automatic refreshes.

2. Enable one or more snapshot refresh processes using the initialization parameters SNAPSHOT_REFRESH_PROCESSES, SNAPSHOT_REFRESH_INTERVAL, SNAPSHOT_REFRESH_KEEP_CONNECTIONS. The snapshot refresh processes then examine the automatic refresh time of each snapshot in the database. For each snapshot that is scheduled to be refreshed at or before the current time, one of the snapshot refresh processes performs the following operations:

• re-evaluates the snapshot's NEXT value to determine the next automatic refresh time

• refreshes the snapshot

• stores the next automatic refresh time in the data dictionary

Example I

The following statement creates the simple snapshot EMP_SF that contains the data from a SCOTT's employee table in New York:

CREATE SNAPSHOT emp_sf 

	PCTFREE 5 PCTUSED 60 

	TABLESPACE users 

	STORAGE INITIAL 50K NEXT 50K 

	REFRESH FAST NEXT sysdate + 7 
	AS 
		SELECT * FROM scott.emp@ny 

Since the statement does not include a START WITH parameter, Oracle7 determines the first automatic refresh time by evaluating the NEXT value using the current SYSDATE. Provided a snapshot log currently exists for the employee table in New York, Oracle7 performs a fast refresh of the snapshot every 7 days, beginning 7 days after the snapshot is created.

The above statement also establishes storage characteristics for the table that Oracle7 uses to maintain the snapshot.

Example II

The following statement creates the complex snapshot ALL_EMPS that queries the employee tables in Dallas and Baltimore:

CREATE SNAPSHOT all_emps 
	PCTFREE 5 PCTUSED 60 
	TABLESPACE users 
	STORAGE INITIAL 50K NEXT 50K 
	USING INDEX STORAGE (INITIAL 25K NEXT 25K)
	REFRESH START WITH ROUND(SYSDATE + 1) + 11/24 
	        NEXT NEXT_DAY(TRUNC(SYSDATE, 'MONDAY') + 15/24 
	AS 
		SELECT * FROM fran.emp@dallas 
			UNION 
		SELECT * FROM marco.emp@balt 

Oracle7 automatically refreshes this snapshot tomorrow at 11:00am. and subsequently every Monday at 3:00pm. Since this command does not specify either fast or complete refreshes, Oracle7 must decide how to refresh the snapshot. Since ALL_EMPS is a complex snapshot, Oracle7 must perform a complete refresh.

The above statement also establishes storage characteristics for both the table and the index that Oracle7 uses to maintain the snapshot:

• The first STORAGE clause establishes the sizes of the first and second extents of the table as 50 kilobytes each.

• The second STORAGE clause (appearing with the USING INDEX option) establishes the sizes of the first and second extents of the index as 25 kilobytes each.

Related Topics