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.
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:
CREATE SNAPSHOT emp_sf
PCTFREE 5 PCTUSED 60
STORAGE (INITIAL 50K NEXT 50K PCTINCREASE 50)
START WITH sysdate
NEXT sysdate + 7
AS SELECT * FROM firstname.lastname@example.org;
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.
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:
CREATE SNAPSHOT emp_dallas
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
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:
- If you refresh rarely and want faster query performance, use Method A.
- If you refresh regularly and can sacrifice query performance, use Method B.
Privileges Required to Create Snapshots
To create a snapshot, you must have the following sets of privileges:
- To create a snapshot in your own schema, you must have the CREATE SNAPSHOT, CREATE TABLE, and CREATE VIEW system privileges, as well as SELECT privilege on the master tables.
- To create a snapshot in another user's schema, you must have the CREATE ANY SNAPSHOT system privilege, as well as SELECT privilege on the master table. Additionally, the owner of the snapshot must have been able to create the snapshot.
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.
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.
- 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.
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.
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.
Snapshots are queried just like a table or view. For example, the following statement queries a snapshot named EMP:
SELECT * FROM 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
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:
CREATE SNAPSHOT LOG ON scott.emp
STORAGE (INITIAL 10K NEXT 10K PCTINCREASE 50)
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.
- Oracle creates a table, named MLOG$_master_table_name, to store the ROWID and timestamp of rows updated in the master table. The timestamp column is not updated until the log is first used by a snapshot refresh.
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:
ALTER SNAPSHOT LOG sale_price
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
- One snapshot is not configured to be automatically refreshed by Oracle; it has to be manually refreshed.
- One snapshot has a large refresh interval, such as every year.
- A network failure has prevented an automatic refresh of one or more of the snapshots based on the master table.
- A network or site failure has prevented a dropped snapshot from unregistering itself from its master.
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:
DBMS_SNAPSHOT.PURGE_LOG( master => emp,
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:
- All simple snapshots of a master table have been dropped.
To drop a local snapshot log, use the SQL command DROP SNAPSHOT LOG, as in
- All simple snapshots of a master table are to be completely refreshed, not fast refreshed.
DROP SNAPSHOT LOG emp_log;
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.
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:
- Generally a simple snapshot should use fast refreshes because they are more efficient than complete refreshes.
- If the master tables receive predictable updates, automatically refresh the associated snapshots at the appropriate interval.
- After bulk loads to the master tables, manually refresh all snapshots based on the master tables. This propagates the new rows of the master tables to associated snapshots.
- If you need to refresh a collection of snapshots to a single point in time, such as when there is a parent/child relationship between a pair of snapshots, use snapshot refresh groups.
Privileges Required to Refresh a Snapshot
To refresh a snapshot, you must meet the following criteria:
- You must own the snapshot or have the ALTER ANY SNAPSHOT system privilege.
- The snapshot owner (or the user that you have connected as, if you are using a database link) must have SELECT privileges on the master table and, for fast refreshes, on the snapshot log.
Automatically Refreshing Snapshots
If you want to have your snapshots automatically refreshed at a periodic interval, you must complete the following steps:
- You must specify a snapshot refresh interval and type of refresh.
- You must have one or more SNP background processes that wake up periodically and refresh any snapshots that are due to be refreshed.
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:
- The dates or date expressions in the START WITH and NEXT parameters of an individual snapshot, or INTERVAL and NEXT_DATE parameters in the procedure calls for a refresh group, must evaluate to a future point in time. The INTERVAL value is evaluated immediately before the refresh begins.
Thus, you should select an interval that is greater than the length of time required to perform a refresh. A date literal must be enclosed in single quotes, while date expressions do not require quotes.
Table 3 - 1 lists some common date expressions used for snapshot refresh intervals.
- If a snapshot should be refreshed periodically at a set interval, use the NEXT or INTERVAL parameter with a date expression similar to "SYSDATE + 7". For example, if you set the automatic refresh interval to "SYSDATE + 7" on Monday, but for some reason, such as network failure, the snapshot is not refreshed until Thursday, "SYSDATE + 7" now evaluates to every Thursday, not Monday. If you always want to refresh a snapshot group at a specific time automatically, regardless of the last refresh (for example, every Monday), the INTERVAL or NEXT parameters should specify a date expression similar to "NEXT_DAY(TRUNC(SYSDATE), 'MONDAY')".
Table 3 - 1. Examples of Common Refresh Intervals
|SYSDATE + 7
||Exactly seven days from the last refresh
|SYSDATE + 1/48
||Every half hour
||Every Monday at 3 PM
||The first Thursday of each quarter
The following example shows valid combinations for specifying a refresh interval:
CREATE SNAPSHOT snap
. . .
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.finance, 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.
- You can manually refresh an existing snapshot refresh group.
- You can manually refresh one or more snapshots, that may, or may not, be part of one or more refresh groups.
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:
CREATE DATABASE LINK sales.hq.com USING 'hq.sales.com';
CREATE SNAPSHOT mysnap AS SELECT * FROM email@example.com;
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:
CREATE SNAPSHOT mysnap AS SELECT * FROM firstname.lastname@example.org;
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:
CREATE DATABASE LINK sales.hq.com CONNECT TO scott
IDENTIFIED BY tiger USING 'sales.hq.com';
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: