Re: MV refresh
From: Bradd Piontek <piontekdd_at_gmail.com>
Date: Thu, 27 Aug 2009 15:55:00 -0500
Message-ID: <e9569ef30908271355j18584f3ak8fd61244ac7198e6_at_mail.gmail.com>
By default, in 10g and later, DBMS_MVIEW.REFRESH will do an atomic refresh (DELETE followed by DML and commit). It is transactionaly consistent. The mview shouldn't end up empty.however, if you wanted to trap errors (assuming this isn't run via dbms_scheduler or dbms_job?) you could scripts it just like any other sql script, or wrap it in PL/SQL and capture the exceptions. Bradd Piontek
"Next to doing a good job yourself,
Date: Thu, 27 Aug 2009 15:55:00 -0500
Message-ID: <e9569ef30908271355j18584f3ak8fd61244ac7198e6_at_mail.gmail.com>
By default, in 10g and later, DBMS_MVIEW.REFRESH will do an atomic refresh (DELETE followed by DML and commit). It is transactionaly consistent. The mview shouldn't end up empty.however, if you wanted to trap errors (assuming this isn't run via dbms_scheduler or dbms_job?) you could scripts it just like any other sql script, or wrap it in PL/SQL and capture the exceptions. Bradd Piontek
"Next to doing a good job yourself,
the greatest joy is in having someone
else do a first-class job under your
direction."
- William Feather
On Thu, Aug 27, 2009 at 3:50 PM, Roger Xu <wellmetus_at_gmail.com> wrote:
> Hi MV gurus,
>
> Is it possible that when a MV is being refreshed but gets some kind of
> error and ends up an empty MV in 10g?
>
> exec DBMS_MVIEW.refresh('MYMV','C'); - here is how we refresh.
>
> The application team wants me to write a procedure to refresh MV and
> capture the error message if it fails and restore the MV to the previous
> state. How do I capture the error? I know I can show errors in SQL*PLUS.
>
> Thanks,
>
> Roger Xu
>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Aug 27 2009 - 15:55:00 CDT