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"
<Madhusudana.Reddy_at_bestbuy.com> 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"
> <Madhusudana.Reddy_at_bestbuy.com> 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: Madhusudana.Reddy_at_bestbuy.com
> >
> > 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: ListGuru_at_fatcity.com (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: jack_silvey_at_yahoo.com
>
> 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: ListGuru_at_fatcity.com (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: Madhusudana.Reddy_at_bestbuy.com
>
> 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: ListGuru_at_fatcity.com (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: jack_silvey_at_yahoo.com
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: ListGuru_at_fatcity.com (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: Madhusudana.Reddy_at_bestbuy.com
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: ListGuru_at_fatcity.com (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).
Received on Mon Jun 24 2002 - 00:03:18 CDT
