How Oracle Snapshots Work
Oracle Tips by Burleson Consulting
The Data Warehouse Development Life Cycle
Distributed Oracle Data Warehouses
HOW ORACLE SNAPSHOTS WORK
For snapshots small enough to be totally repopulated, the following
steps are necessary. Note that it is possible to do a REFRESH
COMPLETE or a REFRESH FORCE rather than a cron job, but using a cron
is a simple way to guarantee that the replicated table will be fully
repopulated. (A cron job is a method of scheduling tasks inside
UNIX) Oracle also allows the REFRESH FAST option as an alternative
from fully re-populating the replicated table. With REFRESH FAST,
Oracle keeps copies of all of the table changes and moves them to
the replicated table a specific intervals that are specified when
the snapshot is created. To avoid the REFRESH FAST option with a
Unix cron, the following two steps are required:
1. Create the snapshot with the REFRESH COMPLETE option.
2. Alter oracle.cron to drop and re-create the snapshot.
For snapshots on large tables, you may want to use the REFRESH FAST
option. For REFRESH FAST, the following three steps are required:
1. On the destination system--Create the snapshot with the
REFRESH FAST option signed on as user SYS. (Be sure to define a
database link with CONNECT TO XXX IDENTIFIED BY ZZZ and ensure that
user XXX has select privileges against the master table.
2. On the master system --Create a snapshot log on each
3. Bounce the DESTINATION SYSTEM to begin the refreshes based on the
interval specified in the CREATE SNAPSHOT statement.
Listing 9.1 shows
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