Re: Refresh Materialized view manually
From: ddf <oratune_at_msn.com>
Date: Tue, 2 Jun 2009 12:15:22 -0700 (PDT)
Message-ID: <97b75b10-d8cb-484c-8cff-fc3b8cab4809_at_g20g2000vba.googlegroups.com>
On Jun 2, 2:08 pm, ddf <orat..._at_msn.com> wrote:
> On Jun 2, 2:04 pm, naga.cha..._at_gmail.com wrote:
>
>
>
>
>
> > On Jun 2, 2:58 pm, ddf <orat..._at_msn.com> wrote:
>
> > > On Jun 2, 1:42 pm, naga.cha..._at_gmail.com wrote:
>
> > > > On Jun 2, 2:32 pm, ddf <orat..._at_msn.com> wrote:
>
> > > > > On Jun 2, 1:10 pm, Nag <naga.cha..._at_gmail.com> wrote:
>
> > > > > > Hi,
> > > > > > We have a materialized view which was scheduled through dbms_jobs. At
> > > > > > the same time, we want to have capability to refresh manually when
> > > > > > some event happens.
>
> > > > > > Is is it possible to do this? Do we have any issues with this?
>
> > > > > > We are on 10gR2 RAC with three nodes.
>
> > > > > > Your input will be appreciated.
>
> > > > > > Thanks,
> > > > > > Naga
>
> > > > > You can execute the refresh job any time you'd like, presuming it's
> > > > > not currently running. You don't provide much detail on what sort of
> > > > > 'event' needs to happen to get a refresh started. Provide more detail
> > > > > regarding this request so someone can possibly assist you further.
>
> > > > > David Fitzjarrell
>
> > > > We refresh this view every 30 seconds automatically.
> > > > Sometimes underlying tables will be updated by users and they want to
> > > > generate a report right away with the updated data.
> > > > They don't want to wait next 30 seconds, for the latest data. So want
> > > > to provide a manually refresh so that latest data will updated in
> > > > materialized view.
>
> > > > Thanks,
> > > > Naga- Hide quoted text -
>
> > > > - Show quoted text -
>
> > > You refresh this every 30 seconds and you have users that won't wait
> > > that long? My thoughts are to let the users wait; 30 seconds is not
> > > an unreasonable interval, and I expect the 30-second interval was
> > > scheduled because you could not refresh at a shorter interval.
>
> > > David Fitzjarrell- Hide quoted text -
>
> > > - Show quoted text -
>
> > David,
> > Thanks for your suggestion.
> > But I just want to get an idea what would be the best way to manully
> > refresh.
>
> > I really appreciate your input.
>
> > Thanks,
> > Naga- Hide quoted text -
>
> > - Show quoted text -
>
> I've provided that information:
>
> execute the refresh job manually using the dbms_job.run procedure.
>
> David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -
Date: Tue, 2 Jun 2009 12:15:22 -0700 (PDT)
Message-ID: <97b75b10-d8cb-484c-8cff-fc3b8cab4809_at_g20g2000vba.googlegroups.com>
On Jun 2, 2:08 pm, ddf <orat..._at_msn.com> wrote:
> On Jun 2, 2:04 pm, naga.cha..._at_gmail.com wrote:
>
>
>
>
>
> > On Jun 2, 2:58 pm, ddf <orat..._at_msn.com> wrote:
>
> > > On Jun 2, 1:42 pm, naga.cha..._at_gmail.com wrote:
>
> > > > On Jun 2, 2:32 pm, ddf <orat..._at_msn.com> wrote:
>
> > > > > On Jun 2, 1:10 pm, Nag <naga.cha..._at_gmail.com> wrote:
>
> > > > > > Hi,
> > > > > > We have a materialized view which was scheduled through dbms_jobs. At
> > > > > > the same time, we want to have capability to refresh manually when
> > > > > > some event happens.
>
> > > > > > Is is it possible to do this? Do we have any issues with this?
>
> > > > > > We are on 10gR2 RAC with three nodes.
>
> > > > > > Your input will be appreciated.
>
> > > > > > Thanks,
> > > > > > Naga
>
> > > > > You can execute the refresh job any time you'd like, presuming it's
> > > > > not currently running. You don't provide much detail on what sort of
> > > > > 'event' needs to happen to get a refresh started. Provide more detail
> > > > > regarding this request so someone can possibly assist you further.
>
> > > > > David Fitzjarrell
>
> > > > We refresh this view every 30 seconds automatically.
> > > > Sometimes underlying tables will be updated by users and they want to
> > > > generate a report right away with the updated data.
> > > > They don't want to wait next 30 seconds, for the latest data. So want
> > > > to provide a manually refresh so that latest data will updated in
> > > > materialized view.
>
> > > > Thanks,
> > > > Naga- Hide quoted text -
>
> > > > - Show quoted text -
>
> > > You refresh this every 30 seconds and you have users that won't wait
> > > that long? My thoughts are to let the users wait; 30 seconds is not
> > > an unreasonable interval, and I expect the 30-second interval was
> > > scheduled because you could not refresh at a shorter interval.
>
> > > David Fitzjarrell- Hide quoted text -
>
> > > - Show quoted text -
>
> > David,
> > Thanks for your suggestion.
> > But I just want to get an idea what would be the best way to manully
> > refresh.
>
> > I really appreciate your input.
>
> > Thanks,
> > Naga- Hide quoted text -
>
> > - Show quoted text -
>
> I've provided that information:
>
> execute the refresh job manually using the dbms_job.run procedure.
>
> David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -
You can also use the dbms_refresh.refresh procedure, supplying the name of the object to refresh:
SQL> exec dbms_refresh.refresh('EMP_MV')
PL/SQL procedure successfully completed.
SQL> David Fitzjarrell Received on Tue Jun 02 2009 - 14:15:22 CDT