New to this topic – in my current environment we are not doing any replication but we use materialized views frequently in order to achieve performance gains on complex views that are in turn used by web applications (obviously these are only used when the data involved is not completely time-sensitive, we do nightly refreshes of the materialized views). Note that we are doing Complete, On Demand refreshes on a specified schedule.
The issue I am running into is it seems that Oracle has used some internal process to determine the 'master' table and when DML operations occur on the master, the MV becomes invalid. To be honest we don't really care whether the master table has any inserts, updates, deletes as we just want to completely refresh the MV every night – unfortunately when the MV goes invalid it prevents refreshes.
So my question is can I manually set the master table to something like SYS.DUAL so that the MV never goes invalid or is there another workaround?
Failing this I suppose instead of using MVs I would just schedule a nightly CTAS.
Edit:
Added the Oracle note # in comments below – synopsis is that when the MV is in the same database as the master table, DML on master table will cause the MV to go invalid ("Needs Recompile" specifically). Certainly I can schedule a nightly recompile but this doesn't seem elegant.
To downvoter – this is my 2nd question here, could you express what makes the question so flawed so I don't keep losing points in future posts? 🙂
Best Answer
From the Oracle documentation
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.