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.
