Updatable Snapshots

Updatable Snapshots, a feature of Oracle Enterprise Edition
only, is the snapshot, or copy, of a table, or part of a table.
This copy can also be updated. Thus, updatable snapshot sites can also propagate any
changes made upon them back to the parent.

‘Updatable Snapshots’ should not be confused with the
replication feature ‘Single–Master, updatable
snapshots’ available in Standard Edition. To clarify
further, the following should be considered:

1. Single master, multi-updatable snapshots is possible in Standard Edition also.

2. The ‘updatable’ snapshots term used by Oracle, in its
   documentation, is in the sense a ‘READ ONLY’ Snapshot
   created at the Snapshot site, which would be updated
   by the master site from time to time. Vice versa
   would not be possible, as you will appreciate that this is a ‘SINGLE MASTER’ Replication.

3. Now, lets take an example as follows:

   > create snapshot snp1 refresh fast start with sysdate
     next sysdate + 4 as select * from tab1;

   The above query will create a read-only snapshot. Of
   course you can create this in the standard edition as well. This
   snapshot will be updated from time to time by the
   master table tab1.

4. Now, consider this example:

   >create snapshot snp1 refresh fast start with sysdate
     next sysdate + 4
     for update query rewrite
     as select * from tab1;

   This will create an updateable snapshot in the real, Enterprise-only sense.
   (Mark the word ‘for update’ in the script). If you run
   the above script in the standard edition, Oracle will
   return the following message:

   ORA-00439 : feature not enabled.

   Clearly, this updatable snapshot can only be created
   in the Enterprise Edition, and any changes made directly on
   this snapshot will be reflected back to the master
   table.

5. Snapshot replication, either in Standard / Enterprise Editions may
   be sloppy at times, especially in the production OLTP and very dynamic databases on WANs.

For additional information on Oracle replication solutions, check out Prakash’s complete article on Oracle Replication Solutions.

Prakash