Table of Contents Previous Next


7 Built-In Packages : 7.6 DBMS_MVIEW

Use procedures in the DBMS_MVIEW package to manage and refresh materialized views and their dependencies. Advanced Server provides support for the following DBMS_MVIEW procedures:
GET_MV_DEPENDENCIES(list VARCHAR2, deplist VARCHAR2);
The GET_MV_DEPENDENCIES procedure returns a list of dependencies for a specified view.
REFRESH(list VARCHAR2, method VARCHAR2, rollback_seg VARCHAR2 , push_deferred_rpc BOOLEAN, refresh_after_errors BOOLEAN , purge_option NUMBER, parallelism NUMBER, heap_size NUMBER , atomic_refresh BOOLEAN , nested BOOLEAN);
This variation of the REFRESH procedure refreshes all views named in a comma-separated list of view names.
REFRESH(tab dbms_utility.uncl_array, method VARCHAR2, rollback_seg VARCHAR2, push_deferred_rpc BOOLEAN, refresh_after_errors BOOLEAN, purge_option NUMBER, parallelism NUMBER, heap_size NUMBER, atomic_refresh BOOLEAN, nested BOOLEAN);
This variation of the REFRESH procedure refreshes all views named in a table of dbms_utility.uncl_array values.
REFRESH_ALL_MVIEWS(number_of_failures BINARY_INTEGER, method VARCHAR2, rollback_seg VARCHAR2, refresh_after_errors BOOLEAN, atomic_refresh BOOLEAN);
The REFRESH_ALL_MVIEWS procedure refreshes all materialized views.
REFRESH_DEPENDENT(number_of_failures BINARY_INTEGER, list VARCHAR2, method VARCHAR2, rollback_seg VARCHAR2, refresh_after_errors BOOLEAN, atomic_refresh BOOLEAN, nested BOOLEAN);
This variation of the REFRESH_DEPENDENT procedure refreshes all views that are dependent on the views listed in a comma-separated list.
REFRESH_DEPENDENT(number_of_failures BINARY_INTEGER, tab dbms_utility.uncl_array, method VARCHAR2, rollback_seg VARCHAR2, refresh_after_errors BOOLEAN, atomic_refresh BOOLEAN, nested BOOLEAN);
This variation of the REFRESH_DEPENDENT procedure refreshes all views that are dependent on the views listed in a table of dbms_utility.uncl_array values.
Advanced Server's implementation of DBMS_MVIEW is a partial implementation when compared to Oracle's version. Only those functions and procedures listed in the table above are supported.
When given the name of a materialized view, GET_MV_DEPENDENCIES returns a list of items that depend on the specified view. The signature is:
GET_MV_DEPENDENCIES(
list IN VARCHAR2,
deplist OUT VARCHAR2);
list specifies the name of a materialized view, or a comma-separated list of materialized view names.
deplist is a comma-separated list of schema-qualified dependencies. deplist is a VARCHAR2 value.
7.6.2 REFRESH
Use the REFRESH procedure to refresh all views specified in either a comma-separated list of view names, or a table of DBMS_UTILITY.UNCL_ARRAY values. The procedure has two signatures; use the first form when specifying a comma-separated list of view names:
REFRESH(
list IN VARCHAR2,
method IN VARCHAR2 DEFAULT NULL,
rollback_seg IN VARCHAR2 DEFAULT NULL,
push_deferred_rpc IN BOOLEAN DEFAULT TRUE,
refresh_after_errors IN BOOLEAN DEFAULT FALSE,
purge_option IN NUMBER DEFAULT 1,
parallelism IN NUMBER DEFAULT 0,
heap_size IN NUMBER DEFAULT 0,
atomic_refresh IN BOOLEAN DEFAULT TRUE,
nested IN BOOLEAN DEFAULT FALSE);
REFRESH(
tab IN OUT DBMS_UTILITY.UNCL_ARRAY,
method IN VARCHAR2 DEFAULT NULL,
rollback_seg IN VARCHAR2 DEFAULT NULL,
push_deferred_rpc IN BOOLEAN DEFAULT TRUE,
refresh_after_errors IN BOOLEAN DEFAULT FALSE,
purge_option IN NUMBER DEFAULT 1,
parallelism IN NUMBER DEFAULT 0,
heap_size IN NUMBER DEFAULT 0,
atomic_refresh IN BOOLEAN DEFAULT TRUE,
nested IN BOOLEAN DEFAULT FALSE);
list is a VARCHAR2 value that specifies the name of a materialized view, or a comma-separated list of materialized view names. The names may be schema-qualified.
tab is a table of DBMS_UTILITY.UNCL_ARRAY values that specify the name (or names) of a materialized view.
method is a VARCHAR2 value that specifies the refresh method that will be applied to the specified view (or views). The only supported method is C; this performs a complete refresh of the view.
rollback_seg is accepted for compatibility and ignored. The default is NULL.
push_deferred_rpc is accepted for compatibility and ignored. The default is TRUE.
refresh_after_errors is accepted for compatibility and ignored. The default is FALSE.
purge_option is accepted for compatibility and ignored. The default is 1.
parallelism is accepted for compatibility and ignored. The default is 0.
heap_size IN NUMBER DEFAULT 0,
heap_size is accepted for compatibility and ignored. The default is 0.
atomic_refresh is accepted for compatibility and ignored. The default is TRUE.
nested is accepted for compatibility and ignored. The default is FALSE.
The following example uses DBMS_MVIEW.REFRESH to perform a COMPLETE refresh on the public.emp_view materialized view:
Use the REFRESH_ALL_MVIEWS procedure to refresh any materialized views that have not been refreshed since the table or view on which the view depends has been modified. The signature is:
REFRESH_ALL_MVIEWS(
number_of_failures OUT BINARY_INTEGER,
method IN VARCHAR2 DEFAULT NULL,
rollback_seg IN VARCHAR2 DEFAULT NULL,
refresh_after_errors IN BOOLEAN DEFAULT FALSE,
atomic_refresh IN BOOLEAN DEFAULT TRUE);
number_of_failures is a BINARY_INTEGER that specifies the number of failures that occurred during the refresh operation.
method is a VARCHAR2 value that specifies the refresh method that will be applied to the specified view (or views). The only supported method is C; this performs a complete refresh of the view.
rollback_seg is accepted for compatibility and ignored. The default is NULL.
refresh_after_errors is accepted for compatibility and ignored. The default is FALSE.
atomic_refresh is accepted for compatibility and ignored. The default is TRUE.
The following example performs a COMPLETE refresh on all materialized views:
Upon completion, errors contains the number of failures.
Use the REFRESH_DEPENDENT procedure to refresh all material views that are dependent on the views specified in the call to the procedure. You can specify a comma-separated list or provide the view names in a table of DBMS_UTILITY.UNCL_ARRAY values.
REFRESH_DEPENDENT(
number_of_failures OUT BINARY_INTEGER,
list IN VARCHAR2,
method IN VARCHAR2 DEFAULT NULL,
rollback_seg IN VARCHAR2 DEFAULT NULL
refresh_after_errors IN BOOLEAN DEFAULT FALSE,
atomic_refresh IN BOOLEAN DEFAULT TRUE,
nested IN BOOLEAN DEFAULT FALSE);
REFRESH_DEPENDENT(
number_of_failures OUT BINARY_INTEGER,
tab IN DBMS_UTILITY.UNCL_ARRAY,
method IN VARCHAR2 DEFAULT NULL,
rollback_seg IN VARCHAR2 DEFAULT NULL,
refresh_after_errors IN BOOLEAN DEFAULT FALSE,
atomic_refresh IN BOOLEAN DEFAULT TRUE,
nested IN BOOLEAN DEFAULT FALSE);
number_of_failures is a BINARY_INTEGER that contains the number of failures that occurred during the refresh operation.
list is a VARCHAR2 value that specifies the name of a materialized view, or a comma-separated list of materialized view names. The names may be schema-qualified.
tab is a table of DBMS_UTILITY.UNCL_ARRAY values that specify the name (or names) of a materialized view.
method is a VARCHAR2 value that specifies the refresh method that will be applied to the specified view (or views). The only supported method is C; this performs a complete refresh of the view.
rollback_seg is accepted for compatibility and ignored. The default is NULL.
refresh_after_errors is accepted for compatibility and ignored. The default is FALSE.
atomic_refresh is accepted for compatibility and ignored. The default is TRUE.
nested is accepted for compatibility and ignored. The default is FALSE.
The following example performs a COMPLETE refresh on all materialized views dependent on a materialized view named emp_view that resides in the public schema:
Upon completion, errors contains the number of failures.

7 Built-In Packages : 7.6 DBMS_MVIEW

Table of Contents Previous Next