Using Updateable Snapshots in Oracle

With the updateable snapshot enhancement of Oracle, you can create an updateable snapshot.
An updateable snapshot may contain only a subset of the target master table's data columns. When the remote table is changed, the change is propagated back to the master table. Prior to Oracle, all snapshots were read-only, and end users were required to access the master table to make updates. Oracle has lifted this restriction with the new multimaster replication scheme, whereby a snapshot table may act as a master table. Whenever the snapshot is updated, the update is transported back to the original master table.
If the CASCADE option is specified when you set up an updateable snapshot, the master table replicates the updates from the updateable snapshot to all of the other replicated sites.
Updateable snapshots have some very compelling benefits:
Updateable snapshot: A snapshot that supports insert, update, and delete on the remote site.

1. Users can query and update a local replicated data set even when disconnected from the master site.
2. Space is saved on remote sites because updateable snapshots have smaller footprints than multimaster replicated tables.

A primary key snapshot is updateable if the snapshot has been created as part of a snapshot group and the snapshot contains the FOR UPDATE clause. The SQL code for creating an updateable primary key snapshot might look like this:

CREATE SNAPSHOT GROUP CUST_SNAPS
. . . .
CREATE SNAPSHOT CUST_SNAP 
FOR UPDATE  -- This makes the snapshot updateable
AS
SELECT * 
FROM CUSTOMER @ DENVER;

In this example, we have defined a snapshot group called CUST_SNAPS. Within this group, we see a snapshot called CUST_SNAPthat contains the FOR UPDATEclause.
The next lesson reviews another new feature for snapshots called deferred constraints.

Click the Quiz link below to test your understanding of new snapshot features.
Using Updateable Snapshots - Quiz