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 --- [EMAIL PROTECTED] 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 > <[EMAIL PROTECTED]> > <ctrassens@YA cc: > > > HOO.COM> Subject: > Re: dbms_mview.refresh and dba_mviews.compile_state > > Sent by: > > > root@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. > > > --- [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
