Oracle Distributed Systems

Snapshot Creation Basics

The CREATESNAPSHOT syntax contains several components, allowing the creator to manipulate the snapshot’s physical storage, its refresh interval, and even what rollback segments to use when it refreshes if you are using Oracle8.

Let’s examine the components of this statement one at a time, using the following snapshot creation statement as a sample (line numbers are included for reference):

1   CREATE SNAPSHOT product_prices
 2   PCTFREE 0 PCTUSED 99
 3   TABLESPACE sprocket_data
 4       STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0)
 5   USING INDEX TABLESPACE sprocket_indx
 6       STORAGE (INITIAL 128K NEXT 128K PCTINCREASE 0)
 7   REFRESH FAST
 8       START WITH sysdate
 9       NEXT sysdate + 1
10   WITH PRIMARY KEY
11   USING LOCAL ROLLBACK SEGMENT rb_large
12   AS
13       SELECT  product_id,
14               catalog_number,
15               price,
16               effective_date
17       FROM [e-mail];

This statement creates a snapshot of the PRODUCT_PRICES table, mastered in database PHQS.BIGWHEEL.COM.

The Snapshot STORAGE Clause

Lines 1 through 6 specify the name of the snapshot, PRODUCT_PRICES, and specify storage parameters for the snapshot’s base table and the primary key index:

1   CREATE SNAPSHOT product_prices
 2   PCTFREE 0 PCTUSED 99
 3   TABLESPACE sprocket_data
 4       STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0)
 5   USING INDEX TABLESPACE sprocket_indx
 6       STORAGE (INITIAL 128K NEXT 128K PCTINCREASE 0)

Note

The primary key index is an Oracle8 feature; the USING INDEX syntax does not exist in Oracle7.

You will notice that we chose extreme values ...