Re: Refresh Materialized view manually

From: Palooka <nobody_at_nowhere.com>
Date: Tue, 02 Jun 2009 23:34:52 +0100
Message-ID: <gOhVl.6$fL.2_at_newsfe15.ams2>

ddf wrote:
> 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

OP's design stinks. If they need real time replication, snapshots ^H^H^H^H^H^H^H^H^H Materialized Views are not the solution. 30 seconds? LOL!

Palooka Received on Tue Jun 02 2009 - 17:34:52 CDT