Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.misc -> Re: Newbie question re refreshing Materialized Views

Re: Newbie question re refreshing Materialized Views

From: <>
Date: Thu, 16 Aug 2007 11:03:35 -0700
Message-ID: <>

On Aug 16, 12:09 pm, Adam Cameron <> wrote:
> >> Doesn't this strike you as being quite crap?
> > No, and you wouldn't, either, but apparently you don't understand that
> > truncate is a DDL operation, not DML, and doesn't generate undo, the
> > primary mechanism for consistent reads. If no undo exists you can't
> > construct a read-consistent view of the data.
> OK, so the mechanism it uses the effect the refresh is suboptimal in that
> it works in such a way as one cannot reliably query the view, because it
> could be in the middle of a refresh, and return no rows. I'd rather it
> just *error* if it can't give me a straight answer ("ORA-12345: dunno at
> the moment, sorry", or something). As it stands, I cannot tell whether I
> got no rows because there ARE no rows matching my query, or because it's
> halfway through an operation that SHOULD be atomic but isn't, and it
> *currently* has no rows because it's between "truncate" and "repopulate".
> Would it not be sensible to engage some sort of lock around this whole
> process (however it is achieved), so that my query is told "just hold yer
> horses for a sec, I can't answer that just now as I'm in the middle of
> finding that out for myself!", rather than just going "here you go... an
> incorrect answer".
> I suppose there is some bigger picture @ work here that I'm just not
> getting. Which is fine. I'd just like to "get it".
> --
> Adam

As TRUNCATE is DDL, not DML,. two 'implied' commits occur, once before the truncate does the dirty deed, and once after. As such, there isn't any way to 'lock' this and provide your ORA-56789 Hell if I know what data is there, I'm piddling with a refresh so ask me later error message. In 10g the data is deleted (DML, no commit, implied or otherwise) so the behaviour you expect (returning a read-consistent image from the time before the refresh began) would occur.

Possibly that explains the situation.

David Fitzjarrell Received on Thu Aug 16 2007 - 13:03:35 CDT

Original text of this message