Re: oracle materialized view - refesh occuring but base tables not being deleted

From: Anurag Varma <>
Date: Fri, 15 Feb 2008 12:08:28 -0800 (PST)
Message-ID: <>

On Feb 15, 10:36 am, wrote:
> hi,
> we have an application which uses materialized views. the refresh
> action (using fast refresh) executes without error but for some reason
> the base materialized views (<schema>.MLOG$_<table>) does not get
> deleted (we do a select count(1) from the base table). as i understand
> it the entries are supposed to be removed. a few additional peices of
> information:
> * the base tables are only used by one materialized view.
> * there have been problems with the refresh over the past few weeks
> failing until we increased the refesh interval (controlled by the
> application)
> * there are over 100,000,000 entries in the base table.
> i'm going to be investigating this in more detail myself over the next
> few days and will add anything i find.
> g

Always specify what version of oracle you are referring to. And read the docs:

For 9i:

For 10g:

Read the section: "Restrictions on Fast Refresh on Materialized Views with Aggregates"

Note the restriction: "COUNT(*) must be specified." count(coumn_name) alone won't work if there are insert/deletes and updates happening.

Anurag Received on Fri Feb 15 2008 - 14:08:28 CST

Original text of this message