Database Administration (MOSC)

MOSC Banner

Invalid materialized view

edited Nov 26, 2021 10:56AM in Database Administration (MOSC) 2 commentsAnswered

Hi team,

In our 12.1.0.2 database I have 3 materialized views which are in invalid state. I have checked with this query and it seems that they are unusable:

SELECT MVIEW_NAME,  UNUSABLE, KNOWN_STALE, INVALID 
FROM DBA_MVIEW_ANALYSIS 
WHERE OWNER = 'APPS' 
AND INVALID = 'Y';


also have checked last DDL:

select object_name,last_ddl_time,status from dba_objects where object_name in ('AS_SUBORDINATE_REPS_MV','AS_GROUP_MV','LNS_LOAN_DTLS_ALL_MV');



As i have read from several sources DML on underlaying MV tables can invalidate MV itself. So my thoughts to compile or refresh those views.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center