Hello,
I have a materialized view whose definition looks like this:
CREATE MATERIALIZED VIEW <owner>.<materialized view name> (<column list>)
TABLESPACE <tablespace name>
PCTUSED 0
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY
AS
<materialized view query>;
The <materialized view query> is quite a complicated query involving a few tables, not any view, and is refreshed nightly via a job.
What is going on is that, during the day, periodically, the materialized view, and the ones based on it, are set to an INVALID state,
as can be seen by inspecting the user_objects view. A successful recompilation makes the problem disappear only temporarily and that's quite annoying.
Could you shed some light on the reasons why the mv gets invalidated and how to avoid it?
Now, I realize this one is by no means a great question and it may lack a lot of information.
But I was not able to sort it out in any other way and browsing the net did not prove helpful.
Thanks in advance for any help you will provide.
Warm regards
Livio
Structural change to an underlying table is a common cause
SQL> create table T as select * from dba_Objects where object_id is not null;
Table created.
SQL>
SQL> alter table T add primary key ( object_id);
Table altered.
SQL>
SQL> drop MATERIALIZED VIEW MV;
Materialized view dropped.
SQL>
SQL> CREATE MATERIALIZED VIEW MV
2 BUILD IMMEDIATE
3 REFRESH FORCE ON DEMAND
4 WITH PRIMARY KEY
5 AS
6 select owner, count(object_id)
7 from t
8 group by owner;
Materialized view created.
SQL>
SQL> select object_type, status from user_Objects where object_name = 'MV';
OBJECT_TYPE STATUS
----------------------- -------
MATERIALIZED VIEW VALID
TABLE VALID
SQL>
SQL> alter table T add x int;
Table altered.
SQL>
SQL> select object_type, status from user_Objects where object_name = 'MV';
OBJECT_TYPE STATUS
----------------------- -------
MATERIALIZED VIEW INVALID
TABLE VALID
Check the LAST_DDL_TIME on objects in your query.