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

Latest Articles