Name

CREATE SNAPSHOT

Synopsis

CREATE SNAPSHOT
[schema.]snapshot_name
   [PCTFREE integer]
   [PCTUSED integer]
   [INITRANS integer]
   [MAXTRANS integer]
   [LOGGING | NOLOGGING]
   [CACHE | NOCACHE]
[CLUSTER cluster_name
(clustercolumnlist) ]
   [TABLESPACE tablespace_name]
   [STORAGE (
      [INITIAL integer[K | M] ]
      [NEXT integer[K | M] ]
      [MINEXTENTS integer[K | M] ]
      [MAXEXTENTS {integer | UNLIMITED} ]
      [PCTINCREASE integer]
      [FREELISTS integer]
      [FREELIST GROUPS integer]
      ) ]
   [NOPARALLEL]
[PARALLEL ( {DEGREE {integer | DEFAULT} |
INSTANCES {integer | DEFAULT} } ) ]
   [USING INDEX
      [TABLESPACE tablespace_name]
      [PCTFREE integer]
      [INITRANS integer]
      [MAXTRANS integer]
      [STORAGE (
          [INITIAL integer[K | M] ]
          [NEXT integer[K | M] ]
          [MINEXTENTS integer]
[MAXEXTENTS {integer | UNLIMITED} ]
          [PCTINCREASE integer]
          [FREELISTS integer]
          [FREELIST GROUPS integer]
        ) ]
    ]
   [REFRESH
      [FAST | COMPLETE | FORCE]
      [START WITH date]
      [NEXT date]
      [WITH PRIMARY KEY]
      [WITH ROWID]
      [USING DEFAULT [MASTER | LOCAL ] ROLLBACK SEGMENT]
[USING [MASTER | LOCAL] ROLLBACK SEGMENT
rollback_segment]
 ]
[FOR UPDATE ] AS snapshot_query

Creates a snapshot (snapshot_name), which is the result of a query run against one or more tables or views.

Keywords

PCTFREE

Specifies the percentage of space to be reserved in each data block for future updates to rows contained in that block. Valid values are - 99, and the default value is 10.

PCTUSED

Specifies the minimum percentage of space that will be maintained as used in each data block. The value may be in the range 1- 99, and the default value is 40.

INITRANS

Specifies the initial number of transaction entries allocated to each block.

MAXTRANS

Specifies the maximum number of transaction entries allocated to each block.

LOGGING

Specifies that all changes to the snapshot are logged in the redo logs. This is the default.

NOLOGGING

Specifies that changes to the snapshot are not logged. This improves performance, but requires that the snapshot be recreated from the base tables in the event that the database must be rebuilt.

CACHE

Specifies that full table scans of the snapshot will be placed at the front of the LRU list. This is useful for small lookup or dimension tables.

NOCACHE

Specifies that full table scans of the snapshot will not place data blocks at the start of the LRU list. This is the default.

CLUSTER

Specifies that the snapshot is to be created in the specified cluster (cluster_name). Since the cluster has its own storage specifications, do not use the STORAGE clauses with the CLUSTER clause.

TABLESPACE

Specifies the name of the tablespace in which this snapshot will be created. The default is the default tablespace for the schema owner.

STORAGE

Specifies the physical characteristics of the snapshot as follows:

INITIAL

Specifies the size of the first extent for this snapshot in bytes, kilobytes, or megabytes. If this value is not a multiple of the database blocksize, it will be rounded up to a multiple of the database blocksize.

NEXT

Specifies the size of the next extent in bytes, kilobytes, or megabytes. If this value is not a multiple of the database blocksize, it will be rounded up to a multiple of the database blocksize.

MINEXTENTS

Specifies the number of extents to be allocated when this snapshot is created. The minimum and default value is 1.

MAXEXTENTS

Specifies the maximum number of extents that may be allocated for this snapshot. The default will vary according to the database blocksize. Specify UNLIMITED for unlimited expansion.

PCTINCREASE

Specifies the percentage by which each extent will grow over the previous extent. The default is 50, which means that each extent will be one-and-one-half times larger than the previous extent.

FREELISTS

Specifies the number of free lists contained in each freelist group in this snapshot. The default is 1 and the maximum depends on the database blocksize.

FREELIST GROUPS

Specifies the number of groups of free lists for this snapshot. The default is 1. This parameter should be used only with the Parallel Server option running in parallel mode.

NOPARALLEL

Specifies that no parallel processing will be supported for the snapshot.

PARALLEL

Specifies the level of parallelism to be supported, based on the following parameters:

DEGREE

Specifies the degree of parallelism. An integer value specifies how many slave processes can be used. Specify DEFAULT to use the default value for the tablespace.

INSTANCES

Specifies the number of instances that can be used to execute slave processes. Specify DEFAULT to use the default value specified for the tablespace.

USING INDEX

Specifies the storage parameters for the index that Oracle uses to maintain the snapshot. The TABLESPACE (TABLESPACE tablespace_name) and STORAGE parameters have the meanings shown earlier in the keyword list for the snapshot.

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 is the default, and means that Oracle will either decide that a FAST refresh is possible or do a COMPLETE refresh.

START WITH

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

NEXT

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

WITH PRIMARY KEY

Specifies that the snapshot is created as a primary key snapshot. That is, Oracle keeps track of the changes by using the primary key rather than ROWID. This allows the master table to be reorganized without impacting the ability to perform a fast refresh.

WITH ROWID

Specifies that the snapshot is created as a ROWID snapshot. This is for compatibility with Oracle 7.3.

USING DEFAULT ROLLBACK SEGMENT

Specifies the rollback segment to be used as a default if a rollback segment is not explicitly supplied. Select either MASTER (use the default rollback segment at the remote master for the individual snapshot) or LOCAL (use the default rollback segment for the local refresh group that contains the snapshot).

USING ROLLBACK SEGMENT rollback_segment

Specifies the rollback segment to use when refreshing the snapshot. Specify either MASTER (the rollback segment to be used at the remote master for the individual snapshot) or LOCAL (the rollback segment to be used for the local refresh group that contains the snapshot).

FOR UPDATE

Allows the simple snapshot to be updated. If used with Oracle8 replication, the changes will be propagated back to the master database.

AS snapshot_query

Provides the actual SQL query used to populate the snapshot, and is subject to the same restrictions as a view.

Notes

You must have the CREATE SNAPSHOT privilege to create a snapshot in your own schema, or the CREATE ANY SNAPSHOT privilege to create a snapshot in another schema. In addition, you must have sufficient privileges to create a table, view, and index, since these are created as part of the snapshot creation process, and the owner of the schema containing the snapshot must have appropriate privileges to execute the snapshot’s query.

The script dbmssnap.sql must have been run by SYS to create the SNAPSHOT package before attempting to create a snapshot.

Since Oracle appends 7-character identifiers to the snapshot name when creating snapshot objects in the schema, you should limit the snapshot name to 27 characters or less.

Example

The following example creates a snapshot of scott’s emp table, which is located on a server called UK. The snapshot will be populated tomorrow and then every seven days from today:

CREATE SNAPSHOT uk_emp
   REFRESH COMPLETE
   START WITH SYSDATE+1
   NEXT SYSDATE+7
   AS SELECT * FROM scott.emp@UK
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset