Document ID: 23209.1
Subject: (V7) SNAPSHOTS -- COMMON QUESTIONS & ANSWERS
Last Revision Date: 12 April 1995
Author: SUVISWAN
This bulletin is a collection of common questions and answers on table
snapshots in Oracle7.
Q: What is a table snapshot ?
A: A snapshot is a read-only copy of a table, a subset of a table, or data
multiple tables and can be maintained at individual nodes of a
distributed database.
Q: Describe some characteristics of snapshots.
A: a. A snapshot is defined by a distributed query that references one or
more master tables, views or other snapshots.
b. Table snapshots are read-only: they can only be queried, not updated.
Note: In RDBMS version 7.1.6 and beyond, snapshots can even be updated.
This is part of the Symmetric Replication feature.
c. Each copy of the master table is called a 'snapshot' because the copy
is periodically refreshed to reflect a recent, consistent state of the
master table. The refresh can be automatic or manual.
d. Snapshots can contain data from multiple master tables. Conversely,
several snapshots created at different nodes can be based on a single
master table.
Q: What are the two types of snapshots ?
A: A SIMPLE snapshot is based on one table and its defining query does not
have GROUP BY or CONNECT BY clauses, or subqueries, joins or set
operations. If a snapshot's defining query contains any of these clauses
or operations, it is called a COMPLEX snapshot.
Q: How would I prepare the database to create snapshots ?
A: a. You would need to run the CATSNAP.SQL script, connected INTERNAL or as
SYS at both the database that contains the master table and the
database that will contain the snapshot(s). The location of this script
is operating system specific; in UNIX, the script is typically located
in the
$ORACLE_HOME/rdbms/admin directory.
Note: This script is run automatically by CATPROC.SQL; if you have
the procedural option installed, you've probably run this
script already.
b. The DBMSSNAP.SQL script should be run at both locations, connected
INTERNAL or as SYS. The location of this script is operating system
specific; in UNIX, the location is the
$ORACLE_HOME/rdbms/admin directory.
c. The relevant init.ora parameters need to be set:
SNAPSHOT_REFRESH_PROCESSES (number)
SNAPSHOT_REFRESH_INTERVAL (seconds)
SNAPSHOT_REFRESH_KEEP_CONNECTIONS (true/false)
Q: What do the CATSNAP.SQL and DBMSSNAP.SQL scripts do ?
A: CATSNAP.SQL creates data dictionary structures for storing and
maintaining snapshots. DBMSSNAP.SQL creates packages of procedures used to
refresh snapshots.
Q: Briefly describe the init.ora parameters that need to be set in order to
maintain snapshots.
A: a. SNAPSHOT_REFRESH_PROCESSES
--------------------------
Default : 0
Range of values : 0..10
Multiple instances: May have different values
This parameter sets the number of snapshot refresh processes per
instance. If you wish to have your snapshots updated automatically,
you must set this parameter to a value of one or higher. One snapshot
refresh process will usually be sufficient unless you have a large
number of snapshots which refresh simultaneously.
b. SNAPSHOT_REFRESH_INTERVAL
-------------------------
Default : 60 (one minute)
Range of values : 1..3600 seconds (1 second to 60 minutes)
Multiple instances: May have different values
This parameter sets the interval between wake-ups for the snapshot
refresh processes on the instance.
c. SNAPSHOT_REFRESH_KEEP_CONNECTIONS
---------------------------------
Default : FALSE
Range of values : TRUE / FALSE
Multiple instances: May have different values
This parameter specifies whether the snapshot refresh processes should
keep remote database connections after all snapshots are refreshed. If
set to FALSE, remote database connections made for the purpose of
refreshing snapshots are closed after all the refreshes are complete.
Q: What are the privileges required to create a snapshot ?
A: a. To create a snapshot in one's own schema, one should have the CREATE
SNAPSHOT, CREATE TABLE, CREATE VIEW and CREATE INDEX (only for simple
snapshots) system privileges, as well as SELECT privilege on the master
tables.
b. To create a snapshot in another user's schema, one must have the CREATE
ANY SNAPSHOT, CREATE ANY TABLE, CREATE ANY VIEW and CREATE ANY INDEX
(only for simple snapshots) system privileges, as well as SELECT
privilege on the master tables.
c. The owner of the snapshot should also have sufficient quota on the
tablespace intended to hold the snapshot.
Q: Give an example of the CREATE SNAPSHOT command.
A: To create a simple snapshot EMP_SF based on the EMP table on the remote
node NY:
CREATE SNAPSHOT emp_sf
PCTFREE 5
PCTUSED 60
TABLESPACE users
STORAGE ( INITIAL 50k
NEXT 50k
PCTINCREASE 0)
REFRESH FAST
START WITH SYSDATE
NEXT SYSDATE + 7
AS SELECT * FROM emp@ny where deptno = 10;
Q: What happens when a snapshot is created in the database ?
A: When a snapshot is created, several operations are performed internally by
ORACLE:
a. ORACLE creates a table named SNAP$_snapshotname at the snapshot node.
This table stores the rows retrieved by the snapshot's defining query
and is referred to as the base table of the snapshot.
b. ORACLE creates a read-only view on the SNAP$ table for queries issued
against the snapshot. This view is created at the snapshot node as well
and uses the same name as that in the CREATE SNAPSHOT command.
c. Another local view named MVIEW$_snapshotname is created and used by
ORACLE when it refreshes the snapshot.
d. If the snapshot is simple, ORACLE creates an index named
PK$_snapshotname on the SNAP$ table.
Q: Can the internal objects created by ORACLE during snapshot creation be
altered or deleted ?
A: DO NOT alter the data in the SNAP$ table in any way. Triggers or
integrity constraints should NEVER be added to this table. DO NOT alter,
change data in, or delete these objects.
Q: Describe the three methods available to refresh snapshots.
A: There are three methods for refreshing snapshots: FAST, COMPLETE and
FORCE.
a. A FAST refresh uses the snapshot log of a master table to refresh the
snapshot. Only simple snapshots can execute a fast refresh. The rows
in the master table that have changed since the last refresh are
incorporated into the SNAP$ table. A FAST refresh will raise an error
if the snapshot log does not exist.
b. A COMPLETE refresh entirely replaces the data in a simple or complex
snapshot.
c. FORCE refresh is the default option and performs a FAST refresh when
possible; otherwise it uses a COMPLETE refresh.
Q: How can a snapshot be set up to refresh automatically ?
A: A refresh option (FAST, COMPLETE or FORCE) should be specified in the
REFRESH clause of the CREATE SNAPSHOT or ALTER SNAPSHOT command. The
START WITH parameter specifies the time for a snapshot's initial
automatic refresh. If this parameter is omitted, the initial automatic
refresh is evaluated using the creation time and the NEXT parameter.
Q: How can you manually refresh a snapshot ?
A: The 'REFRESH' stored procedure of the DBMS_SNAPSHOT package can be used
to manually refresh a snapshot. The syntax is:
EXECUTE dbms_snapshot.refresh (' [ schema.] name ' [, 'refresh_option'])
For example, to do a FAST refresh of the EMP snapshot:
EXECUTE dbms_snapshot.refresh ('emp', ' f ' ) ;
Refresh Options: 'F' or 'f' = FAST refresh
'C' or 'c' = COMPLETE refresh
'?' = DEFAULT refresh option
Q: What is a snapshot log ?
A: A snapshot log is a table located in the same database as the master
table. The snapshot log table lists changes that have been made to the
master table, and information about which snapshots have and have not
been updated to reflect those changes. It is used by simple snapshots
to perform a FAST refresh, but cannot be used by a complex snapshot.
The snapshot log is created by the CREATE SNAPSHOT LOG command.