| Invalid MView's in DBA_OBJECTS [message #357497] |
Wed, 05 November 2008 11:10  |
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  |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
