Hi,
After deploying updates to our database, we look for invalid objects. Materialized views are showing up with a status of INVALID in dba_objects and compile_state of NEEDS_COMPILE if any DML affects underlying tables.
We are trying to find a way to differentiate between an mview that is legitimately broken and one that has stale data. Just updating the data appears to put it in a state that looks like it is broken:
SQL> create table tim.t (id number, name varchar2(10));
Table TIM.T created.
SQL> alter table tim.t add constraint t_pk primary key (id);
Table TIM.T altered.
SQL> insert into tim.t values (1, 'tim');
1 row inserted.
SQL> commit;
Commit complete.
SQL> create materialized view tim.m as select * from tim.t;
Materialized view TIM.M created.
SQL> insert into tim.t values (2, 'jane');
1 row inserted.
SQL> commit;
Commit complete.
SQL> select mview_name, compile_state, staleness, last_refresh_date, fast_refreshable,
2 refresh_mode, refresh_method, build_mode, status
3 from dba_mviews m
4 join dba_objects o on o.owner = m.owner and o.object_name = m.mview_name and o.object_type = 'MATERIALIZED VIEW'
5* ;
MVIEW_NAME COMPILE_STATE STALENESS LAST_REFRESH_DATE FAST_REFRESHABLE REFRESH_MODE REFRESH_METHOD BUILD_MODE STATUS
_____________ ________________ ________________ ____________________ ___________________ _______________ _________________ _____________ __________
M NEEDS_COMPILE NEEDS_COMPILE 11-FEB-26 DIRLOAD_DML DEMAND FORCE IMMEDIATE INVALID
How can we check what is broken and what is just stale?
Thanks.
(Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production)