Invalid materialized view
                                                 Last update (2008-06-02 12:16:59)
                                                                                                                   Date added (2008-06-02 11:45:06)

Summary
As far it concerns MATERIALIZED VIEWS possible invalidation is different than the unusable state. You don't have to worry when you see invalid MATERIALIZED VIEWS, unusable state is that counts!

The state of a materialized view can be checked by querying the view DBA_MVIEW_ANALYSIS

The column UNUSABLE: takes a value of Y or N and advises whether the materialized view may be used.
The column KNOWN_STALE: also takes a value of Y or N and advises whether a materialized view is known to be stale
and finally column INVALID: will be set to Y if the materialized view is invalid and N if it is not.
SELECT MVIEW_NAME,  UNUSABLE, KNOWN_STALE, INVALID 
FROM DBA_MVIEW_ANALYSIS 
WHERE OWNER = 'APPS' 
AND INVALID = 'Y';

MVIEW_NAME                     U K I
------------------------------ - - -
AS_FORECAST_MV                 N Y Y
AS_SUBORDINATE_REPS_MV         N Y Y
AS_GROUP_MV                    N Y Y
BIC_OPPORTUNITIES_MV           N Y Y
CORRECTION_DESC                N N Y
SR_CUST_NAME_ORG               N N Y
ASF_ROLLUP_MGR_MV              N N Y
ASF_SC_BIN_MV                  N N Y
XXE_TT_SZF_FOREIS              N N Y
XXE_TT_CITIES_SNAPSHOT         N N Y
XXE_TT_SZF_FOREIS_ATTR_SNAP    N N Y

MVIEW_NAME                     U K I
------------------------------ - - -
XXE_TT_SZF_PARTIES_DATA        N N Y
MANDATE_BANKS                  N N Y
PAYMENT_SOURCES                N N Y

14 rows selected.
From the previous example all the mviews are invalid, but further investigation shows that no one is unusable.

Dependencies related to materialized views are automatically maintained to ensure correct operation. At DDL time, a materialized view depends on the detail tables referenced in its definition.

Therefore, any DDL operation, such as a DROP or ALTER, on any dependency in the materialized view will cause it to become invalid.

A materialized view is automatically revalidated whenever it is referenced. However, if a column has been dropped in a table referenced by a materialized view or the owner of the materialized view didn't have one of the query rewrite privileges and that has now been granted to them, the command
ALTER MATERIALIZED VIEW  mview_name ENABLE QUERY REWRITE
should be used to revalidate the materialized view and, if there are any problems, an error will be returned.
Reviews
Categories
Oracle DBA-> (147)
  Alertlog (3)
  Archivelog (5)
  Auditing (1)
  Backup - Recovery (19)
  Database files (6)
  Database options (2)
  DB links (2)
  Dependencies (1)
  Dictionary (2)
  Exp-Imp Datapump (6)
  Jobs (2)
  Mview (2)
  Networking (3)
  Objects (7)
  Parameters (2)
  Redologs (6)
  Roles - Grants (2)
  Rollback - Undo (8)
  Segments (3)
  Sequences (2)
  Sessions (14)
  SGA (14)
  Tables (10)
  Tablespaces (10)
  Temp (4)
  Toad (5)
  Transactions (1)
  Upgrade (2)
  Users (3)
Oracle APPS DBA-> (66)
Exadata (1)
Performance Tuning-> (59)
Oracle Real Cases (24)
Oracle Errors (23)
Oracle SQL tricks (32)
Oracle RAC (3)
Oracle Security (8)
Filters
Search