Oracle7 Server Distributed Systems Manual, Vol. 2 Go to Product Documentation Library
Go to books for this product
Go to Contents for this book
Go to Index

Go to previous file in sequence Go to next file in sequence

Read-Only Snapshots

This chapter explains how to use read-only snapshots to provide copies of tables at remote sites, without requiring the use of remote queries. This chapter describes how to

Note: The features described in this chapter are available only to users of the distributed option.

Note that most of the activities described in this chapter can be accomplished much more easily by using Oracle's Replication Manager, a GUI interface for replication. See the documentation for Oracle Replication Manager for more information.

Understanding Read-Only Snapshots

As shown in Figure 3 - 1, when you create a read-only snapshot, Oracle creates several internal objects in the schema of the snapshot. Do not alter, change data in, or delete these objects manually. At the snapshot site, Oracle creates a base table, named SNAP$_snapshotname, to store the rows retrieved by the snapshot's defining query. For simple snapshots, Oracle also creates an index on the ROWID column of the base table, named I_SNAP$_snapshotname. You should not alter the base table in any way. Do not add triggers or integrity constraints to the base table, unique indexes or modify their contents.

Figure 3 - 1. Snapshot Architecture

Oracle creates a read-only view of the base table that is used whenever you query the snapshot. This view uses the name that you provided when you issued the CREATE SNAPSHOT statement.

Oracle creates a second local view, named MVIEW$_snapshotname, on the remote master table. When you refresh a snapshot, Oracle uses this view to refresh the snapshot. Oracle stores the results of this query in the base table, replacing the previous snapshot data.

For simple snapshots, you can choose to create a snapshot log for the master table. This log is named MLOG$_master_table_name and the trigger used to update this log is named TLOG$_master_table_name. The information in this log allows you to perform a fast refresh of a simple snapshot.

With a fast refresh, only the changed rows of the snapshot, as indicated by the snapshot log, need to be updated. Each time that you make a change to the master table, Oracle tracks that change in the snapshot log, including the ROWID of the changed row. The generated index (I_SNAP$_) on the ROWID column of the base table allows these changes to be quickly applied to the snapshot. A complex snapshot, or a simple snapshot without a snapshot log, must be completely regenerated from the master table every time you refresh the snapshot. This is known as a complete refresh.

You should not drop the generated index. If you will never perform a fast refresh of the snapshot and do not want this index created, you can create your snapshot as a complex snapshot, for example by joining with DUAL.

A snapshot log can be used by multiple simple snapshots of a single master table. After you refresh a snapshot, any rows in the snapshot log that do not apply to any other snapshots of that master are removed from the snapshot log.

Creating Snapshots

You create a snapshot using the SQL command CREATE SNAPSHOT. As when creating tables, you can specify storage characteristics, extent sizes and allocation, and the tablespace to hold the snapshot, or a cluster to hold the snapshot (in which case all of the previous options do not apply). You can also specify how the snapshot is to be refreshed and the distributed query that defines the snapshot; this is unique to snapshots.

For example, the following CREATE SNAPSHOT statement defines a local snapshot to replicate the remote EMP table located in NY:

      START WITH sysdate
      NEXT sysdate + 7

Whenever you create a snapshot, Oracle immediately fills the base table with the rows returned by the query that defines the snapshot. Thereafter, the snapshot is refreshed as specified by the REFRESH clause; see "Refreshing Snapshots" [*].

Restrictions on Snapshots

Declarative constraints on snapshots and snapshot logs are not supported.

Snapshots of LONG columns are not supported.

Naming Snapshots

Snapshots are contained in a user's schema. A snapshot's name must be unique with respect to other objects in the schema. Although a snapshot name can be up to 30 bytes, keep snapshot names to 19 or fewer bytes. If a snapshot name contains more than 19 characters, Oracle automatically truncates the prefixed names of the underlying table and views, and appends them with a four-digit number to ensure uniqueness. This guarantees that the objects comply with the naming rules for schema objects.

Creating a Clustered Snapshot

You can create a snapshot in a cluster, just as you can a table. For example, the following statement creates a snapshot named EMP_DALLAS in the EMP_DEPT cluster:

   CLUSTER emp_dept
   ... ;

The storage parameters of the cluster's data segment are used for the storage of the clustered snapshot, even if storage parameters are specified for the snapshot.

Creating Complex Snapshots versus Creating Local Views

When creating a complex snapshot, consider an alternative: creating simple snapshots and performing the complex query using a view in the snapshot database. Figure 3 - 2 illustrates the advantages and disadvantages of completing the same operation by the two different methods.

Figure 3 - 2. Two Methods for Complex Snapshots

Complex Snapshot

Method A shows a complex snapshot. The snapshot in Database II exhibits efficient query performance because the join operation has already been completed during the snapshot's refresh. However, complete refreshes must be performed in this case because it is a complex snapshot.

Simple Snapshots with a Joined View

Method B shows two simple snapshots in Database II, as well as a view that performs the join in the snapshots' database. Query performance against the view would not be as good as the query performance against the complex snapshot in Method A. However, the simple snapshots can be more efficiently refreshed using snapshot logs.

In summary, to decide which method to use:

Privileges Required to Create Snapshots

To create a snapshot, you must have the following sets of privileges:

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):

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:


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:


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.

Using Snapshots

Snapshots are queried just like a table or view. For example, the following statement queries a snapshot named EMP:


Attention: Never manipulate data in the base table of a read-only snapshot.

You cannot issue any INSERT, UPDATE, or DELETE statements when using a read-only snapshot; if you do, an error is returned. Although INSERT, UPDATE, and DELETE statements can be issued against the base table for a snapshot, they can corrupt the snapshot. Updates are allowed on the master table only, which must then be refreshed to update the snapshot. If you want to alter the snapshot, you must create it as an updatable snapshot as described in Chapter 5.

Creating Views and Synonyms Based on Snapshots

Views or synonyms can be defined based on snapshots. The following statement creates a view based on the EMP snapshot:

CREATE VIEW sales_dept AS
   SELECT ename, empno
   FROM emp
   WHERE deptno = 10;

Privileges Required to Use a Snapshot

To query a snapshot, you must have the SELECT object privilege for the snapshot, either explicitly or via a role.

Managing Snapshot Logs

A snapshot log is a table, in the same database as the master table for a snapshot, that is associated with the master table. Its rows list changes that have been made to the master table, and information about which snapshots have and have not been updated to reflect those changes. You can create a snapshot log to decrease the amount of processing and time needed to refresh the simple snapshot.

Attention: Snapshot logs cannot be used with complex snapshots.

A snapshot log is associated with a single master table; likewise, a master table can have only one snapshot log. If multiple simple snapshots are based on the same master table, they all use the same snapshot log.

These sections explain how to create, manage, and drop snapshot logs.

Creation Order of a Simple Snapshot and the Snapshot Log

If you are creating a simple snapshot, it is more efficient to create the snapshot log before the snapshot. Figure 3 - 3 illustrates the two orders of creation.

Figure 3 - 3. Creation Order of a Simple Snapshot and the Snapshot Log

In Method A, the first refresh of the snapshot cannot use the log because the log cannot reflect all updates entered between the creation of the snapshot and the creation of the snapshot log; therefore, two complete refreshes are necessary.

In contrast, Method B only requires one complete refresh (when creating the snapshot); subsequent refreshes can immediately use the snapshot log. If the master table is large or a number of simple snapshots are based on the same master table, creating the snapshot log before the snapshots can be much more efficient.

Creating a Snapshot Log

Create a snapshot log in the same database as the master table using the SQL command CREATE SNAPSHOT LOG. You can set storage options for the snapshot log's data blocks, extent sizes and allocation, and tablespace to hold the snapshot log. For example, the following statement creates a snapshot log associated with the EMP table:

    TABLESPACE users
    PCTFREE 5;

Naming Snapshot Logs

Oracle automatically creates the snapshot log in the schema that contains the master table. Since you cannot specify a name for the snapshot log (one is implicitly given by Oracle), uniqueness is not a concern.

The Internals of Snapshot Log Creation

When you create a snapshot log, Oracle performs several operations internally:

Additional Information: Refer to Oracle7 Server Application Developer's Guide to learn more about triggers.

The underlying table for a snapshot log and associated trigger are contained in the same schema as the master table. For both the table and the log, the master_table_name is truncated at 20 bytes (if necessary) and appended with a four-digit number to ensure uniqueness. This guarantees that the objects comply with the naming rules for schema objects.

Attention: Do not alter or change data in these objects.

Privileges Required to Create Snapshot Logs

If you own the master table, you can create an associated snapshot log if you have the CREATE TABLE and CREATE TRIGGER system privileges. If you are creating a snapshot log for a table in another user's schema, you must have the CREATE ANY TABLE and CREATE ANY TRIGGER system privileges. In either case, the owner of the snapshot log must have sufficient quota in the tablespace intended to hold the snapshot log.

The privileges required to create a snapshot log directly relate to the privileges necessary to create the underlying objects associated with a snapshot log.

Setting Storage Parameters for Snapshot Logs

Set a snapshot log's storage options as follows:

Each row in a snapshot log takes approximately 26 bytes (18 bytes for an Oracle ROWID, seven bytes for a timestamp, and one byte for DML type). Use this number to calculate how much space a snapshot log requires, using the procedure described for calculating space required by non-clustered tables in the Oracle7 Server Administrator's Guide.

Altering Snapshot Log Storage Parameters

You can alter a snapshot log's storage parameters using the SQL command ALTER SNAPSHOT LOG. For example:

   PCTUSED 40;

Privileges Required to Alter Snapshot Logs Only the owner of the master table, or a user with the ALTER ANY TABLE system privilege can alter the storage parameters of a snapshot log.

Managing Snapshot Log Space Use

Oracle automatically tracks which rows in a snapshot log have been used during the refreshes of snapshots, and purges these rows from the log so that the log does not grow endlessly. Because multiple simple snapshots can use the same snapshot log, rows used in the refresh of one snapshot may still be needed to refresh another snapshot; Oracle does not delete rows from the log until all snapshots have used them. However, this automated feature can cause a snapshot log to grow indefinitely if a single associated snapshot is never refreshed.

For example, Snapshot EMP_B is regularly refreshed. However, Oracle cannot purge the rows used during the refresh of Snapshot EMP_B because Snapshot EMP_A needs them for its next refresh. This situation occurs when you have several simple snapshots based on the same master table and

Purging the Snapshot Log

Keep the snapshot log as small as possible to minimize the space it uses. To reduce the number of rows in a snapshot log, you can either refresh the snapshots associated with the log, or shrink the log by deleting the rows only required by the Nth least recently refreshed snapshots. To do the latter, execute the PURGE_LOG stored procedure of the DBMS_SNAPSHOT package. For example, to purge the log entries in the example above that are needed only for snapshot EMP_A, the least recently refreshed snapshot, you could execute the following procedure:

                         num    => 1);

Additional Information: The parameters for the PURGE_LOG procedure are described in Table 12 - 187.

Reducing Space Allocation for a Snapshot Log

If a snapshot log grows and allocates many extents, purging the log of rows does not reduce the amount of space allocated for the log. To reduce the space allocated for a snapshot log, first copy the rows in the snapshot log to a new location. Then truncate the log and reinsert the old rows. This avoids having to perform a complete refresh of the dependent snapshots. However, any changes made to the master table between the time that you copy the rows to a new location and when you truncate the log will be lost, until the next complete refresh.

Privileges Required to Delete Rows from a Snapshot Log The owner of a snapshot log or a user with the DELETE ANY TABLE system privilege can purge rows from the snapshot log by executing the PURGE_LOG procedure.

Dropping Snapshot Logs

You can drop a snapshot log independently of its master table or any existing snapshots. You might decide to drop a snapshot log if one of the following is true:

To drop a local snapshot log, use the SQL command DROP SNAPSHOT LOG, as in


Privileges Required to Drop a Snapshot Log

Only the owner of the master table, or a user with the DROP ANY TABLE system privilege can drop a snapshot log.

Using Snapshot Refresh Groups

This section describes the procedures provided in the DBMS_REFRESH package that allow you to create, alter, and delete refresh groups. For more information on automatically refreshing snapshots, see page 3 - 16. These procedures should be called from the site where your snapshots are located (not from the site of their associated master tables).

Creating a Refresh Group

To specify the members of a refresh group and the time interval used to determine when the members of this group should be refreshed, call the MAKE procedure of the DBMS_REFRESH package, as shown in the following example:

    name             => 'acctg', 
    list             => 'acct_rec, acct_pay',
    next_date        => SYSDATE, 
    interval         => 'SYSDATE + 1/24',
    implicit_destroy => TRUE);

This example creates the ACCTG refresh group with two members, ACCT_REC and ACCT_PAY, that will be refreshed every hour. Additional information on setting the refresh interval is provided [*].

The type of refresh performed is determined by the mode that you specified when you created each snapshot. If you did not specify a refresh mode for a snapshot, Oracle performs a fast refresh if possible; otherwise, it performs a complete refresh.

Additional Information: The parameters for the MAKE procedure are described in Table 12 - 59.

Altering a Refresh Group

The DBMS_REFRESH package contains separate procedures for adding new members to a refresh group, removing members from a refresh group, and altering the automatic refresh interval for a refresh group.

Adding Members to a Refresh Group

To add snapshots to a refresh group, call the ADD procedure in the DBMS_REFRESH package, as shown in the following example:

DBMS_REFRESH.ADD( name => 'acctg', 
                  list => 'acct_bill', 
                  lax  => TRUE);

This example adds the ACCT_BILL snapshot to the ACCTG refresh group. Setting the last argument to TRUE lets ACCT_BILL be added to ACCTG even if it must first be removed from another group.

Additional Information: The parameters for the ADD procedure are described in Table 12 - 56.

Removing Members from a Refresh Group

To remove snapshots from a refresh group, call the SUBTRACT procedure in the DBMS_REFRESH package, as shown in the following example:

DBMS_REFRESH.SUBTRACT( name => 'acctg', 
                       list => 'acct_bill');

This example removes the ACCT_BILL snapshot from the ACCTG refresh group. This snapshot will no longer be automatically refreshed. After removing a snapshot from a refresh group, you should either refresh it manually or add it to another refresh group.

If you set IMPLICIT_DESTROY to TRUE in the MAKE call for the refresh group, Oracle automatically deletes the group whenever you subtract the last member.

Additional Information: The parameters for the SUBTRACT procedure are described in Table 12 - 61.

Altering the Refresh Interval

If you want to change how often a snapshot group is refreshed (for example, if you want the snapshots refreshed once a day as opposed to once a week), call the CHANGE procedure in the DBMS_REFRESH package as shown in the following example:

DBMS_REFRESH.CHANGE( name      => 'acctg', 
                     next_date => SYSDATE, 
                     interval  => 'SYSDATE + 1');

This example changes the refresh interval of the ACCTG group to once a day.

Additional Information: The parameters for the CHANGE procedure are described in Table 12 - 57.

Deleting a Refresh Group

If you want to remove all of the snapshots from a refresh group and delete the refresh group, call the DESTROY procedure in the DBMS_REFRESH package, as shown in the following example:

DBMS_REFRESH.DESTROY( name => 'acctg');

This example removes all of the snapshots from the ACCTG refresh group. These snapshots will no longer be automatically refreshed. You must either refresh these snapshots manually, or add the snapshots to new refresh groups.

Additional Information: The parameters for the DESTROY procedure are described in Table 12 - 58.

Refreshing Snapshots

You can refresh a snapshot to make the snapshot reflect a more recent state of its master tables. There are two types of refreshes: a fast refresh and a complete refresh. A fast refresh uses the snapshot log of a master table to refresh a simple snapshot by transmitting only the changes needed to bring the snapshot up to date. Only simple snapshots (that is, those consisting of a subset of rows and columns of a single table) can execute a fast refresh. A complete refresh entirely replaces the existing data in a simple or complex snapshot. Also, snapshots can be refreshed automatically or manually, either individually or in groups.

Consider the following issues when deciding how to refresh a snapshot:

Privileges Required to Refresh a Snapshot

To refresh a snapshot, you must meet the following criteria:

Automatically Refreshing Snapshots

If you want to have your snapshots automatically refreshed at a periodic interval, you must complete the following steps:

Specifying a Snapshot Refresh Interval

If you want to refresh an individual snapshot automatically, specify a refresh interval using the START WITH and NEXT parameters in the REFRESH clause of a CREATE SNAPSHOT or ALTER SNAPSHOT statement. This automatically creates a snapshot refresh group that consists of exactly one snapshot. This refresh group has the same name as the snapshot itself.

If you want to refresh a collection of snapshots to a single point of time automatically, you must create a snapshot refresh group using the DBMS_REFRESH.MAKE procedure. To refresh a snapshot refresh group automatically, supply NEXT_DATE and INTERVAL values when you create the group.

Specifying the Refresh Type

When you refresh a snapshot, you can specify that Oracle perform a FAST, COMPLETE, or FORCEd refresh. You can specify one of these three refresh types in the REFRESH clause of a CREATE SNAPSHOT or ALTER SNAPSHOT statement. The snapshots in a refresh group do not have to have the same refresh type. If you do not specify a refresh type, Oracle performs a FORCEd refresh. FORCE performs a fast refresh if possible, or a complete refresh otherwise.

Starting a Background Process

The snapshot refresh facility works by using job queues to schedule the periodic execution of the DBMS_REFRESH.REFRESH procedure. Job queues require that at least one SNP background process be running. This background process wakes up periodically, checks the job queue, and executes any outstanding jobs. The SNP background processes are controlled by the initialization parameters JOB_QUEUE_PROCESSES and JOB_QUEUE_INTERVAL. For more information on job queues, and the initialization parameters that you must set, see Chapter 10.

Understanding the Snapshot Refresh Interval

When setting a snapshot's refresh interval, understand the following behavior:

Table 3 - 1 lists some common date expressions used for snapshot refresh intervals.

Date Expression Evaluation
SYSDATE + 7 Exactly seven days from the last refresh
SYSDATE + 1/48 Every half hour
NEXT_DAY(TRUNC(SYSDATE), 'MONDAY')+3/24 Every Monday at 3 PM
NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE,'Q'),3), 'THURSDAY') The first Thursday of each quarter
Table 3 - 1. Examples of Common Refresh Intervals


The following example shows valid combinations for specifying a refresh interval:

   . . .
           START WITH '01-JUN-94'
           NEXT sysdate + 7
   AS . . . ;

This statement creates the SNAP snapshot, specifying complete automatic refreshes, the first of which occurs on June 1, 1994, with an automatic refresh interval of seven days from the most recent refresh.

The following command creates the ACCT refresh group, which is composed of three snapshots that will be refreshed every Monday:

      name             => 'acct', 
      list             => 'scott.acct,, scott.inventory', 
      next_date        => SYSDATE, 
      interval         => 'next_day(SYSDATE + 1, ''MONDAY'')', 
      implicit_destroy => TRUE, 
      lax              => TRUE);

Attention: Note that because the interval function evaluates to a literal, it must be enclosed in single quotes. Because you must use two single quotes to represent one single quote within a literal, in this example, the literal MONDAY is enclosed in two sets of single quotes.

Troubleshooting Automatic Refresh Problems

Several factors can prevent the automatic refresh of snapshots: not having an SNP background process, an intervening network or instance failure, or an instance shutdown. You may also encounter an error if you attempt to define a master detail relationship between two snapshots. You should define master detail relationships only on the master tables by using declarative referential integrity constraints; the related snapshots should then be placed in the same refresh group to preserve this relationship. Although not prevented by Oracle, if you attempt to define these constraints at the snapshot level, when refreshed, the snapshots may temporarily enter a state where they violate the integrity constraints that you have defined, producing a runtime error.

When any of the factors described above prevents the automatic refresh of a snapshot group, the group remains due to be refreshed. (Remember, when you specify a refresh interval for an individual snapshot, Oracle automatically creates a refresh group for that snapshot.) See page 9 - 8 for additional troubleshooting information.

Snapshots Failing to Refresh

If Oracle encounters a failure, such as a network failure, when attempting to refresh a snapshot refresh group it attempts the refresh again. The first attempt is made after one minute, the second attempt after two minutes, the third after four minutes, and so on, with the interval doubling between each attempt. When the retry interval exceeds the refresh interval, Oracle continues to retry the refresh at the normal refresh interval.

Thus, snapshot refresh groups due to be refreshed will generally be refreshed automatically shortly after you start an SNP background process or resolve any network failures.

However, if Oracle continues to encounter errors when attempting to refresh a snapshot, it considers the group broken after its sixteenth unsuccessful attempt.

Oracle indicates that a snapshot is broken by setting the BROKEN column of the USER_REFRESH and USER_REFRESH_CHILDREN views to Y.

The errors causing Oracle to consider a snapshot refresh group broken are recorded in a trace file. After you have corrected these errors, you must manually refresh the group by calling the procedure DBMS_REFRESH.REFRESH described on 3 - 19. This resets the broken flag to N, and automatic refreshes will proceed from this point.

Snapshots Continually Refreshing

If you encounter a situation where your snapshots are being continually refreshed, you should check the refresh interval that you specified. This interval is evaluated before the snapshot is refreshed. If the interval that you specify is less than the amount of time it takes to refresh the snapshot, the snapshot will be refreshed each time the SNP background process checks the queue of outstanding jobs.

Additional Information: The name of the snapshot trace file is of the form SNPn, where n is platform specific. Consult your platform-specific Oracle documentation for the name on your system.

Snapshot Logs Growing Without Bounds

If a snapshot log is growing without bounds check for snapshots that were dropped but remain registered, You may need to purge part of the log by calling DBMS_SNAPSHOT.PURGE_LOG, as described [*].

Manually Refreshing Snapshots

If you want to manually force a refresh to occur, you have two options.

Manually Refreshing a Snapshot Refresh Group

To refresh a refresh group manually, call the REFRESH procedure in the DBMS_REFRESH package, as shown in the following example:


This example causes the refresh of the ACCTG group to occur immediately instead of waiting for the next automatic refresh interval. Manually refreshing a refresh group does not affect the next automatic refresh of the group.

Additional Information: The parameter for the REFRESH procedure is described in Table 12 - 60.

Manually Refreshing One or More Snapshots

To consistently refresh one or more snapshots that are not members of the same refresh group, use the REFRESH procedure in the package DBMS_SNAPSHOT.

This command allows you to provide a comma-separated list of snapshots that you want to refresh to a transaction-consistent point in time. These snapshots can belong to other refresh groups. Refreshing them using this procedure will not affect their regularly scheduled refresh interval if they are part of an automatic snapshot refresh group. However, if they are members of other groups, you should be aware that those groups will no longer be transactionally consistent.

The following example performs a complete refresh of SCOTT.EMP, a fast refresh of SCOTT.DEPT, and a default refresh of SCOTT.SALARY. By default, DBMS_SNAPSHOT.REFRESH refreshes the snapshot using the mode specified when the snapshot was created. If no mode was specified, the default mode is FORCE.

DBMS_SNAPSHOT.REFRESH( list   => 'scott.emp, scott.dept, scott.salary',
                       method => 'CF');

Additional Information: The parameters for the REFRESH procedure are described in Table 12 - 188.

Snapshot Refresh and Remote Databases

You must fully qualify remote database names when issuing a CREATE SNAPSHOT statement or snapshot refreshes may fail due to naming and privilege conflicts. For example, if you created a snapshot using the following commands:

CONNECT scott/tiger

then refreshes of the snapshot will fail if the user is not SCOTT.

Instead, you should replace the above CREATE SNAPSHOT statement with the following:


Your manual refreshes should then succeed. Automatic refreshes, however, will only work if the username and password strings are embedded in the database link, as shown in the following statement:

     IDENTIFIED BY tiger USING '';

The username and password that you specify allow the SNP background process to connect to the database. For the refresh to succeed, this user must have SELECT privileges on the master table and, for fast refreshes, the snapshot log.

Listing Snapshot Information

Query the DBA_SNAPSHOTS catalog view to obtain a listing of all of the snapshots in a database. Query DBA_SNAPSHOT_LOGS for information about logs for your database.

Listing Refresh Group Information

The data dictionary contains the following views that have information about refresh groups:

Go to previous file in sequence Go to next file in sequence
Prev Next
Copyright © 1996 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Go to books for this product
Go to Contents for this book
Go to Index