Home » RDBMS Server » Server Administration » Invalid MView's in DBA_OBJECTS (Oracle 10g)
Invalid MView's in DBA_OBJECTS [message #357497] Wed, 05 November 2008 11:10 Go to next message
shail_rh
Messages: 10
Registered: November 2007
Location: USA
Junior Member
Hi All,
I am querying dba_objects view to see invalid objects in my database.It's showing status for some of the materialized views as invalid even though these materialized views are being refreshed on daily basis and being used in application.

I know that to make their status valid I can complile them but I don't understand if they are invalid, how come refresh never failed. Also, their status is valid in dba_snapshots.

Any help would be appreciated.

Thanks in advance.


Thanks
Shailendra
Re: Invalid MView's in DBA_OBJECTS [message #464103 is a reply to message #357497] Tue, 06 July 2010 04:40 Go to previous message
suntrupth
Messages: 13
Registered: July 2010
Junior Member
Hi Shailendra,

Dependencies related to MVs are automatically maintained to ensure correct operation. When an MV is created, the materialized view depends on the master tables referenced in its definition. Any DML operation, such as an INSERT, or DELETE, UPDATE, or DDL operation on any dependency in the materialized view will cause it to become invalid.

Though the status is INVALID, the MV can still be queried. However, the query on MV will not return the latest data in master table unless the MV is refreshed.

Example :
SQL> select object_name, object_type, status from user_objects where object_type = 'MATERIALIZED VIEW';

OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------ -------
EMP_MV_PK MATERIALIZED VIEW INVALID

SQL> select mview_name, compile_state from user_mviews;

MVIEW_NAME COMPILE_STATE
------------------------------ -------------------
EMP_MV_PK NEEDS_COMPILE

SQL> select count(*) from emp_mv_pk;

COUNT(*)
----------
2686976

On next refresh, the status becomes VALID.

Example :
SQL> execute dbms_mview.refresh('emp_mv_pk');
SQL> select object_name, object_type, status from user_objects where object_type = 'MATERIALIZED VIEW';

OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------ -------
EMP_MV_PK MATERIALIZED VIEW VALID

SQL> select mview_name, compile_state from user_mviews;

MVIEW_NAME COMPILE_STATE
------------------------------ -------------------
EMP_MV_PK VALID

"REFRESH ON COMMIT" MVs are exception to this behavior because they are refreshed at the same time of DML.

Thanks,
Suntrupth
Previous Topic: Error in Udump trace file
Next Topic: shrink table space
Goto Forum:
  


Current Time: Wed Dec 07 20:05:58 CST 2016

Total time taken to generate the page: 0.08223 seconds