X-list: oracle-l Return-Path: Subject: RE: materialized views From: Michael Fontana Message-id: 87E07E8CF4B26D4587451BE788F5C3215D0807@IAD-WPRD-XCHB02.corp.verio.net Date: 2006-01-05 23:28:50 How are you refreshing them? Mviews are sourced at some foreign destination. Look at the dba_mview row for each carefully to detect the distributed source database. Chances are it is not available. This must be reconciled or they will never be "valid". Of course, if they're not being used, they could easily be dropped. Do you care to divulge the 3rd party vendor software? That would certainly help. > -----Original Message----- > From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] > On Behalf Of Edward Lewis > Sent: Thursday, January 05, 2006 9:53 AM > To: oracle-l@freelists.org > Subject: materialized views > > Hello, > I'm supporting a 3rd party applicaton running > on AIX 5.2L with Oracle 9.2.0.7. > The app makes use of materialized views. > Many of them over time become invalid, after which > I recompile them and they're fine. But eventually, > they become invalid again. The vendor says that this > is because the materialized views were imported into > the database, and must be refreshed again. I did that, > but still have the same problems. > > Any ideas why this happens ? > > I checked v$sql and it appears that the MV's are not being used. > Is there something else I can check to verify this ? > > It appears all the MV's have the following characteristics : > build immediate, refresh complete on demand. > Query rewrite is not set in the MV definition. > > Here are the database settings for the following : > > query_rewrite_enabled = false > query_rewrite_integrity = enforced > > Your input is appreciated. > > ed > > > > -- > Edward Lewis > eglewis@fastmail.fm > > -- > http://www.fastmail.fm - A no graphics, no pop-ups email service > > -- > http://www.freelists.org/webpage/oracle-l > -- http://www.freelists.org/webpage/oracle-l