Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Mar 13, 2002

Updatable Snapshots

By Prakash Chandani

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.


Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM