Materialized view became INVALID and USER_MVIEWS.COMPILE_STATE becomes ‘NEEDS_COMPILE’

This is just intended to demonstrate redo usage of various insert operations rather than answer the whole question. Results on my 10g instance are not 100% deterministic, but the broad picture remained the same each time I ran through.

For the heap tables, I do not know why the insert /*+ append */ generated more redo.

testbed:

create table heap_noappend(id integer, dummy char(500));
create table heap_append(id integer, dummy char(500));
create global temporary table gtt_noappend(id integer, dummy char(500));
create global temporary table gtt_append(id integer, dummy char(500));
create global temporary table gtt_results(stage integer, val integer);

test:

insert into gtt_results(stage, val)
select 0, value from v$statname join v$sesstat using(statistic#)
where sid=sys_context('userenv','sid') and name='redo size';

insert into heap_noappend(id, dummy)
select level, 'A' from dual connect by level<1000;

insert into gtt_results(stage, val)
select 1, value from v$statname join v$sesstat using(statistic#)
where sid=sys_context('userenv','sid') and name='redo size';

insert /*+ append */ into heap_append(id, dummy)
select level, 'A' from dual connect by level<1000;

insert into gtt_results(stage, val)
select 2, value from v$statname join v$sesstat using(statistic#)
where sid=sys_context('userenv','sid') and name='redo size';

insert into gtt_noappend(id, dummy)
select level, 'A' from dual connect by level<1000;

insert into gtt_results(stage, val)
select 3, value from v$statname join v$sesstat using(statistic#)
where sid=sys_context('userenv','sid') and name='redo size';

insert /*+ append */ into gtt_append(id, dummy)
select level, 'A' from dual connect by level<1000;

insert into gtt_results(stage, val)
select 4, value from v$statname join v$sesstat using(statistic#)
where sid=sys_context('userenv','sid') and name='redo size';

result:

select * 
from( select decode(stage,1,'heap noappend',
                          2,'heap append',
                          3,'gtt noappend',
                          4,'gtt append') as operation, 
             val-lag(val) over(order by stage) as redo 
      from gtt_results)
where redo is not null;

OPERATION     REDO                   
------------- ---------------------- 
heap noappend 606932                 
heap append   690768                 
gtt noappend  41488                  
gtt append    256                   

From the Oracle documentation

When DML changes are made to master table data, Oracle Database stores rows describing those changes in the materialized view log and then uses the materialized view log to refresh materialized views based on the master table.1

So you should be able to see what changes are invalidating the MV.

Next you should examine the alert log to see if anything has been logged.

I wonder if you have a complex chain of materialized views? Does one depend on another? Is only one becoming invalid and others are not? Try this query to see what depends on what

dbms_snapshot.get_mv_dependencies( list IN VARCHAR2, deplist OUT VARCHAR2);

I would not recommend your suggested workaround until you understand what changes are invalidating the view.

Edit: Metalink note 264036.1 says:

"This is expected behavior. When there is a DML on the master table, all the MVs based on this table are marked as INVALID. Though the status is INVALID, you will be able to query the mview. However, the query on MV will not return latest update done in master table unless MV is refreshed."

From the way you are using the Materialized Views I am not sure why you cannot issue a refresh of each view that is marked invalid.