Document ID:        38114.1
Subject:            Locking During A Snapshot Refresh
Author:             toscroft
Last Revision Date: 13 May       1996


 
 
Definitions 
----------- 
 
This document refers to several database objects associated with snapshots; 
these are discussed below. 
 
A snapshot is created by performing a select of all or a subset of rows from 
another table. The table which is selected from is referred to as the master 
table. 
 
In addition, when a snapshot is created, ORACLE creates three objects: 
 
1. A table to store the rows retrieved by the snapshot's defining query; 
   this is known as the snapshot base table, SNAP$_<snapshotname>. 
2. A read-only view on the SNAP$_<snapshotname> table which excludes the 
   rowid column. This has a user-defined name specified at snapshot creation. 
3. A local view is created on the master table, which is used for the snapshot 
   refresh. This is called MVIEW$_<snapshotname>. 
 
If the snapshot is to be refreshed with a fast refresh, information on all 
rows updated between refreshes is written to the snapshot log. The snapshot 
log is created on the master table. 
 
When a snapshot log is created ORACLE creates the following: 
 
1. A table to store the rowid and timestamp of all rows changed in the 
   master table, plus the type of operation. This is the snapshot log and 
   is known as MLOG$_<mastertablename>. 
2. A trigger on the master table which fires each time a row is updated, 
   deleted or inserted on the master table. The trigger updates the snapshot 
   log with the information in '1', above, and is known as TLOG$_<mastertable- 
   name>. 
 
The underlying data dictionary tables used are: 
 
On the Master site: 
 
mlog$        - a list of local master tables used by snapshots. 
slog$        - a list of snapshots on local masters. 
 
On the Snapshot site: 
 
snap$        - a list of snapshots  
 
 
Locking During a Refresh 
------------------------ 
 
When a snapshot refresh takes place, the following happens: 
 
a. Sys.snap$ and sys.mlog$ are updated to show the time of refresh. 
b. The snapshot base table is truncated (providing it is not being refreshed 
   as part of a snapshot refresh group). 
c. All rows selected from the master table are inserted into the snapshot 
   base table. 
 
In the case of a fast refresh, there are two additional steps: 
 
a. Sys.slog$ is updated with the time of refresh. 
b. The snapshot log may be purged of rows which are no longer needed for 
   a refresh by any snapshot 
 
Oracle7's read consistent mechanism is used to select a consistent set of 
rows from the master table therefore it is not necessary to take out any 
locks. As should be evident from the steps above, the only locks which will 
be required are those on the relevant rows in the underlying tables (mlog$,  
slog$ and snap$), the snapshot base table and, in the case of a fast refresh, 
the snpashot log. 
 
Where a snapshot refresh group is refreshed, the corresponding row from  
sys.rgroup$ will also be locked. 
 
 
Updatable Snapshots: 
 
With updatable snapshots, it is necessary to temporarily prevent changes 
during a refresh therefore the snapshot base table and the updatable snapshot 
log will be locked exclusively during a refresh, in addition to the locks 
outlined above. The user will see this as a temporary hang while updating 
the snapshot.