Updatable Snapshots

March 13, 2002

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.