Recreate the snapshot to allow fast refresh, (you will have to create a snapshot log on your source table) and refresh once every 5/10/20/30 minutes.
Fast refreshes are just one commit that is either committed or rolled back at the end. Viola, fresh data instantaneously. You can do a refresh immediate when you recreate the snapshots so it will build the data right away. To do it really fast, create a new snapshot with the correct definition, rename the old snapshot, rename the new snapshot to the old name, recompile your packages and procedures, drop the old snapshot, and viola, new snapshot. Snapshots refresh via a job in the Oracle job queue. You can adjust timing on this job to adjust your refresh frequency. Make sure you get your indexes, stats, and grants in place on the new snap too. Check your synonyms as well. hth, jack --- "Reddy, Madhusudana" <[EMAIL PROTECTED]> wrote: > Hello All, > > I have a set of Materialized views in my DB . we > refresh ( COMPLETE) these > MVs, couple of times a day. Web server ( application > ) will hit these MVs to > show the data on web pages. But the complete Refresh > of MVs are consuming > much time and , at this point of time , Application > is not able to show > right data on web pages. This is like a down time. I > need some suggestions > from you all, in order to minimize or zeroing this > down time. > > The first thing I can think of is , FAST refresh , > but one of my Sr.DBA told > me that the MV definition will not allow us for a > FAST refresh( Are there > any limitations for FAST refresh ???? ). Here is a > sample MV Definition : > > CREATE MATERIALIZED VIEW GENRELOB > NOLOGGING > BUILD IMMEDIATE > REFRESH COMPLETE ON DEMAND > DISABLE QUERY REWRITE > AS SELECT DISTINCT > '1' AS CLIP, > LOB.LOB_ID, > LOB.LOB_CD, > GENRE.GENRE_ID, > GENRE.GENRE_DESC, > GENRE.GENRE_DESC AS INSTANCENAME > FROM > GENRE, > GENRE_LOB_XREF, > LOB, > GENRE_PRODUCT_XREF > WHERE > GENRE.GENRE_ID = GENRE_LOB_XREF.GENRE_ID AND > GENRE_LOB_XREF.LOB_ID = LOB.LOB_ID AND > GENRE_PRODUCT_XREF.genre_id = GENRE.genre_id AND > GENRE.DSPLY_IND = 'Y' > ORDER BY > LOB_CD, > GENRE_DESC > ; > > > My Goal is to view the FRESH data on web pages all > the time , irrespective > of MV Refresh. Would anybody suggest me some bright > ideas , to have no or > less down time ??? > > Thanks in advance > Madhu V Reddy > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Reddy, Madhusudana > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California -- Public Internet > access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). __________________________________________________ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).