Jack , DG and ALL, I Have problem in creating the Fast Refresh MVs, from the existing code , which i can not change in present situation. So I am still looking for another option to minimize the down time( blank web pages at the time of MV refresh ) , even by using the COMPLETE refresh . For me space is not a problem ..
I would like to hear some more ideas to eliminate the down time , with the existing MVs ( Complete Refresh ) Hope i hear you all soon , Thanks again Madhu -----Original Message----- Sent: Friday, June 21, 2002 5:33 PM To: Multiple recipients of list ORACLE-L Thanks Mahu. Do you get the feeling that I might have done that a few times? ;) Snapshots and materialized views are the same thing. I guess I might start calling them materialized views in the next version or two, but it is so hard to give up old habits. Another thing you might need to know - you can't easily change a job in the Oracle job queue unless you are the owner, which means that you can't do it as DBA. There is a package called dbms_ijob that will allow you to change jobs as a dba even if you don't own them. There is usually no public synonym for this package, so you will have to refer to it as sys.dbms_ijob. I believe that this package is not officially supported, so you might not find a lot of documentation on it, but I have used it for over a year without any problems. To turn off a snapshot refresh, use the sys.dbms_ijob.broken function. *BE ADVISED* If you unbreak a job in the Oracle job queue, it will try to run immediately. This includes snapshot jobs. If you unbreak a *complete* snapshot refresh job, the first thing it does is truncate the target table. Unbreak a complete snapshot refresh job in the middle of the day and viola, the users suddenly have no data. Be careful. hth, jack --- "Reddy, Madhusudana" <[EMAIL PROTECTED]> wrote: > Jack, > Nice picture of the whole thing . > > Through out your solution , mentioned SNAPSHOT , you > mean Materialized view > ??? > > Thanks, > Madhu > > > > -----Original Message----- > Sent: Friday, June 21, 2002 2:33 PM > To: Multiple recipients of list ORACLE-L > > > 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). > > -- > 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). -- 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).
