If I have materialized view in Oracle which is defined as REFRESH FAST ON COMMIT every 15 minutes. It works when initially created and refreshes happily. What can cause it to stop fast refreshing?
I can see that it has stopped refreshing based on this:
select mview_name, last_refresh_date from all_mviews;
Things we've found seem to stop an MV from refreshing: 1. a change to the base table's DDL 2. if the MV is across a DB link, a problem with getting the data across the link (for example, we had a database here, and one remote. If during a refresh, the connection between the two databases failed, then the refresh failed)
In the case of #1, the refresh fails and never ever works again and we have to recreate the MV. In the case of #2, its been unclear if the refresh will ever pick up again. Fast refresh MVs have proved to be unreliable for us so we've created a job in Windows scheduler to initiate our refreshes.
A thing that occasionally happened during my last job was, that DBA activity sometimes set the parameter job_queue_processes to 0. This will stop materialized view refreshing until the parameter value is set to something greater than 0.
It depends on the materialized view. The following query from the oracle data dictionary will give you a list of all your (the oracle user's) materialized views and how fast refreshable they are.
SELECT MVIEW_NAME, FAST_REFRESHABLE FROM USER_MVIEWS;
The FAST_REFRESHABLE column will give you one of the following values: NO: The materialized view is not fast refreshable, and hence is complex.
DIRLOAD: Fast refresh is supported only for direct loads.
DML: Fast refresh is supported only for DML operations.
DIRLOAD_DML: Fast refresh is supported for both direct loads and DML operations.
DIRLOAD_LIMITEDDML: Fast refresh is supported for direct loads and a subset of DML operations.
The ones that have given me problems in the past have been DIRLOAD_LIMITEDDML. I have usually gotten those if there is a COUNT, MAX, etc... in the MVIEW query. Usually these refresh on inserts and deletes but not on updates.