Oracle Snapshots - How They Work
Oracle Tips by Burleson Consulting
The Data Warehouse Development Life Cycle
Distributed Oracle Data Warehouses
HOW ORACLE SNAPSHOTS WORK
A snapshot is created on a destination system with the CREATE
SNAPSHOT command, and the remote table is immediately defined and
populated from the master table.
After creation, a snapshot may be refreshed periodically. There are
two types of refreshing: complete and fast. A complete refresh can
be done in several ways, but most savvy Oracle developers drop and
re-create snapshots using a Unix cron job to achieve full refreshes,
especially if the table is small and easily re-created. Optionally,
a fast refresh can be used, which refreshes tables with only the
changes made to the master table. This requires additional work on
the slave database to create an Oracle refresh process (in the
init.ora) and the definition of a snapshot log on the master
database (see Figure 9.9).
Figure 9.9 A high-level overview of Oracle snapshots.
Several steps need to be completed before your Oracle data warehouse
is ready to use snapshots. First you need to run CATSNAP.SQL, which
can be found in your $ORACLE_HOME/rdbms/admin directory. This script
will populate the Oracle dictionary with the necessary system tables
to manage the snapshots. You’ll also need to run DBMSSNAP.SQL, which
can also be found in the $ORACLE_HOME/rdbms/admin directory. This
script creates the stored procedures that can be used to manipulate
the snapshots. In addition, the following parameters must be added
to the init.ora file before your Oracle data warehouse can use
* SNAPSHOT_REFRESH_INTERVAL=60--This sets the interval (in minutes)
for the refresh process to wake up.
* SNAPSHOT_REFRESH_PROCESSES=1--This is the number of refresh
processes on the instance (the minimum is 1 refresh per instance).
* SNAPSHOT_REFRESH_KEEP_CONNECTIONS=FALSE--This specifies whether
the database should keep remote connections after refreshing the
tables. Always use FALSE.
This is an excerpt from "High Performance
Data Warehousing", copyright 1997.
like Oracle tuning, you may enjoy the book
Oracle Tuning: The Definitive
Reference , with
over 900 pages of BC's favorite tuning tips &
You can buy it
directly from the publisher and save 30%, and get
instant access to the code depot of Oracle tuning