Tuesday, February 7, 2012 8:44

Scripts for Snapshot

Posted by on Tuesday, November 17, 2009, 23:06
This news item was posted in Oracle category and has 0 Comments so far.

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');

ROWID

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).

REFRESH
changes 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

Leave a Reply

You can leave a response, or trackback from your own site.