Thursday, October 27, 2011

Script to Refresh ALL materialized views in a schema



DECLARE
v_number_of_failures NUMBER(12) := 0;
BEGIN
DBMS_MVIEW.REFRESH_ALL_MVIEWS(v_number_of_failures,'C','', TRUE, FALSE);
END;



ParameterDescription
number_of_failuresReturns the number of failures that occurred during processing
methodA single refresh method indicating the type of refresh to perform for each materialized view that is refreshed. F or f indicates fast refresh, ? indicates force refresh, C or c indicates complete refresh, and Aor a indicates always refresh. A and C are equivalent. If no method is specified, a materialized view is refreshed according to its default refresh method. P or p refreshes by recomputing the rows in the materialized view affected by changed partitions in the detail tables.
rollback_segName of the materialized view site rollback segment to use while refreshing materialized views
refresh_after_errorsIf this parameter is true, an updatable materialized view continues to refresh even if there are outstanding conflicts logged in the DEFERROR view for the materialized view's master table or master materialized view. If this parameter is true and atomic_refresh is false, this procedure continues to refresh other materialized views if it fails while refreshing a materialized view.
atomic_refreshIf this parameter is set to true, then the refreshed materialized views are refreshed in a single transaction. All of the refreshed materialized views are updated to a single point in time. If the refresh fails for any of the materialized views, none of the materialized views are updated.
If this parameter is set to false, then each of the refreshed materialized views is refreshed in a separate transaction.

No comments:

Post a Comment