Oracle in a Nutshell

Name

CREATE/ALTER/DROP SNAPSHOT

Synopsis

CREATE Syntax:

CREATE SNAPSHOT [schema.]snapshot_name
[Physical_Attributes_Clause]
[TABLESPACE tablespace_name]   
[Storage_Clause]
[REFRESH [FAST | COMPLETE | FORCE]
[START WITH date][NEXTREF date]]
AS snapshot_query

ALTER Syntax:

ALTER SNAPSHOT [schema.]snapshot_name
[Physical_Attributes_Clause] [Storage_Clause]
[REFRESH [FAST | COMPLETE | FORCE]
 [START WITH date][NEXTREF date]]

DROP Syntax:

DROP SNAPSHOT [schema.]snapshot_name

Creates, modifies, or removes a snapshot, which is the result of a query run against one or more tables or views. Note that before you can attempt to create a snapshot, the dbmssnap.sql script must be run by SYS to create the built-in DBMS_SNAPSHOT package.

Keywords

snapshot_name

Name of the snapshot. Because Oracle appends 7-character identifiers to the snapshot name when creating snapshot objects in the schema, you should limit snapshot_name to 23 characters or fewer.

TABLESPACE

Specifies the name of the tablespace where this snapshot will be stored. If omitted, the default tablespace for the schema owner will be used.

REFRESH

Specifies the mode and times for automatic refreshes.

FAST

Uses the snapshot log associated with the master table.

COMPLETE

Refreshes by reexecuting the snapshot’s query.

FORCE

Oracle decides if a FAST refresh is possible or if a COMPLETE refresh is necessary; FORCE is the default.

START WITH

Specifies a date for the next automatic refresh time using a standard Oracle date expression.

NEXTREF

Specifies ...