Prasad,
It's a known bug in 8i (bug 1921687 "SNAPSHOTS/
MATERIALIZED VIEWS INCORRECTLY SHOW AS INVALID/ERROR
IN DD VIEWS"). The bug is fixed in 9i.
After DML on the base table the status is changed for
query rewrite purposes, as stated by Christian. The
bug is that the status isn't reset after the refresh
process.
HTH,
- Anita
- Prasada.Gunda1_at_hartfordlife.com wrote:
>
> Thanks for your response. I believe you get a
> different status if the
> dependent objects were changed. In this particular
> case, I know that there
> is nothing changed on the dependent objects.
>
> Oracle document says:
>
> VALID: The materialized view has been validated
> without error, and no
> object upon which it depends has changed since the
> last validation.
>
> NEEDS_COMPILE: Some object upon which the
> materialized view depends has
> changed (other than normal DML changes). An ALTER
> MATERIALIZED VIEW ...
> COMPILE statement is required to validate this
> materialized view.
>
> ERROR: The materialized view has been validated with
> one or more errors
>
> Regards
>
> Christian
>
>
> Trassens To:
> Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> <ctrassens_at_YA cc:
>
>
> HOO.COM> Subject:
> Re: dbms_mview.refresh and dba_mviews.compile_state
>
> Sent by:
>
>
> root_at_fatcity.
>
>
> com
>
> 10/16/2001
>
>
> 02:01 PM
>
>
> Please
>
>
> respond to
>
>
> ORACLE-L
>
>
>
> It is related to query rewrite feature. And it means
> that the dependencies or let me put it in other way
> the tables referenced in the query of the mv have
> been
> altered.
>
> Regards.
>
>
> --- Prasada.Gunda1_at_hartfordlife.com wrote:
> >
> > Hi All,
> >
> > After the creation of a mview,
> > dba_mviews.compile_state shows it is in
> > VALID state.
> >
> > When I refresh this mview using
> dbms_mview.refresh,
> > It shows that the
> > refresh has been completed successfully. But,
> When
> > I look at dba_mviews,
> > compile_state column show ERROR.
> >
> > Where can I see those errors.
> >
> > Thanks in advance for your help.
> >
> > Best Regards,
> > Prasad
Do You Yahoo!?
Find a job, post your resume.
http://careers.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: A. Bardeen
INET: abardeen1_at_yahoo.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Sun Nov 04 2001 - 10:45:48 CST
