Oracle7 Server Distributed Systems Volume II: Replicated Data
Managing Snapshots
This section describes how to manage read-only snapshots.
Operations on a Master Table that Affect Snapshots
All changes made by INSERT, UPDATE, and DELETE statements issued against a table are reflected in associated snapshots when the snapshots are refreshed.
TRUNCATE automatically forces all snapshots of the truncated table to be completely refreshed during their next refresh.
If you drop a master table, any associated snapshots remain and continue to be accessible. An associated snapshot log (if present) of a dropped master table is also dropped. When you attempt to refresh a snapshot based on a non-existent master table, Oracle returns an error.
If you later re-create the master table, the snapshot can again be successfully refreshed, as long as the defining query of the snapshot can be successfully issued against the new master table. You cannot perform a fast refresh of the snapshot, however, until after you re-create the snapshot log. If you cannot successfully refresh the snapshot after dropping and re-creating the master table, you should drop and re-create the snapshot.
Snapshots and Media Failure
As the result of a media failure, either a database that contains a master table of a snapshot or a database with a snapshot may need to be recovered. If a master database is independently recovered to a past point in time (that is, coordinated time-based distributed database recovery is not performed), any dependent remote snapshot that refreshed in the interval of lost time will be inconsistent with its master table. In this case, the administrator of the master database should instruct the remote administrator to perform a complete refresh of any inconsistent snapshot. For additional information on recovering from media failure, refer to your Oracle7 Server Administrator's Guide.
Indexing Snapshots
To increase the query performance when using a snapshot, you can create indexes for the snapshot. To index a column (or columns) of a snapshot, you must create the index on the underlying "SNAP$_" table created to hold the rows of the snapshot.
Attention: Do not use declarative constraints to create an index; instead, use the CREATE INDEX statement (but do not use CREATE UNIQUE INDEX).
Setting Storage Parameters for Snapshots
How you should set storage options for a snapshot depends on the type of snapshot (simple or complex):
- In general, a simple snapshot's storage options should mimic the storage options for its master table, since they share the same characteristics. If a number of master tables are clustered in the master database, you should probably cluster the corresponding snapshots in the remote database.
Note: If a simple snapshot does not duplicate all columns of its master table, modify the snapshot storage items accordingly.
You can change a snapshot's storage parameters using the ALTER SNAPSHOT command. For example, the following command alters the EMP snapshot's PCTFREE parameter:
ALTER SNAPSHOT emp PCTFREE 10;
You cannot change a snapshot's defining query; you must drop the snapshot and then re-create it.
Privileges Required to Alter a Snapshot
To alter a snapshot's storage parameters, the snapshot must be contained in your schema or you must have the ALTER ANY SNAPSHOT and ALTER ANY TABLE system privileges.
Dropping Snapshots
You can drop a snapshot independently of its master tables or the snapshot log. To drop a local snapshot, use the SQL command DROP SNAPSHOT. For example:
DROP SNAPSHOT emp;
If you drop the only snapshot of a master table, you should also drop the snapshot log of the master table, if there is one.
Privileges Required to Drop a Snapshot
Only the owner of a snapshot or a user with the DROP ANY SNAPSHOT system privilege can drop a snapshot.