Normally, creating a snapshot comprise of these steps;
1. create databse link
create database link TEST_DBLINK.US.ORACLE.COM
connect to AMICOS identified by AMICOS
using ‘test’;
2. create snapshot
Create snapshot Test_SnapShot
REFRESH COMPLETE START WITH SYSDATE NEXT SYSDATE+1/24
as select * from A_Table@TEST_DBLINK
If we want to refresh snapshot FAST, we need to do these two steps:
1. create snapshot log on t1 with rowid;
2. create snapshot fb_test_b refresh fast with rowid start with sysdate next sysdate+1/1440 as select * from fb_test_b@my_dblink;
Some AD HOC scripts:
1. Get the refresh time
SELECT NAME,LAST_REFRESH FROM ALL_SNAPSHOT_REFRESH_TIMES;
2. Refresh the data manually
EXEC DBMS_SNAPSHOT.REFRESH(‘Test_SnapShot ‘,’C');
EXEC DBMS_SNAPSHOT.REFRESH(‘Test_SnapShot ‘,’F');
For backwards compatibility, Oracle supports ROWID snapshots in addition to the default, primary key snapshots. A ROWID snapshot is based on the physical row identifiers (ROWIDs) of the rows in a master table. ROWID snapshots should be used only for snapshots based on master tables from an Oracle7 database, and should not be used when creating new snapshots based on master tables from Oracle8 or greater databases (see “Snapshot Log” for more information on the differences between a ROWID and Primary Key snapshot).
REFRESHchanges the mode and times for automatic refreshes: FAST specifies a fast refresh, or a refresh using the snapshot log associated with the master table. COMPLETE specifies a complete refresh, or a refresh that reexecutes the snapshot\'s query. FORCE specifies a fast refresh if one is possible or complete refresh if a fast refresh is not possible. Oracle decides whether a fast refresh is possible at refresh time. If you omit the FAST, COMPLETE, and FORCE options, Oracle uses FORCE by default. START WITH specifies a date expression for the next automatic refresh time. NEXT specifies a new date expression for calculating the interval between automatic refreshes. START WITH and NEXT values must evaluate to times in the future.
References:
http://docstore.mik.ua/orelly/oracle/bipack/ch14_01.htm
http://www.cs.umbc.edu/help/oracle8/server.815/a67791/mview.htm
http://mywebsys.com/oracle/syntax/view_syntax.php?id=21