You can use the stored procedures in the DBMS_MVIEW package to manage and update materialized views and their dependencies.
PolarDB supports the following stored procedures in the
DBMS_MVIEW
package:
Stored procedure | Return type | Description |
---|---|---|
GET_MV_DEPENDENCIES(list VARCHAR2, deplist VARCHAR2); | N/A | Returns a list of dependencies for the 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); | N/A | Updates a list of views separated by commas (,). |
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); | N/A | Updates all the views listed in the DBMS_UTILITY.UNCL_ARRAY table.
|
REFRESH_ALL_MVIEWS(number_of_failures BINARY_INTEGER, method VARCHAR2, rollback_seg VARCHAR2, refresh_after_errors BOOLEAN, atomic_refresh BOOLEAN); | N/A | Updates 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); | N/A | Updates 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); | N/A | Updates all the views that are dependent on the views listed in the DBMS_UTILITY.UNCL_ARRAY table.
|
GET_MV_DEPENDENCIES
After a materialized view is named, you can use the
Example
GET_MV_DEPENDENCIES
stored procedure to retrieve a list of items that are dependent on the specified
view. Syntax:GET_MV_DEPENDENCIES(
list IN VARCHAR2,
deplist OUT VARCHAR2);
ParametersParameter | Description |
---|---|
list | Specifies the name of a materialized view, or a list of materialized view names separated by commas (,). |
deplist | Specifies a list of schema-qualified dependencies separated by commas (,). |
DECLARE
deplist VARCHAR2(1000);
BEGIN
DBMS_MVIEW.GET_MV_DEPENDENCIES('public.emp_view', deplist);
DBMS_OUTPUT.PUT_LINE('deplist: ' || deplist);
END;
In this example, a list of dependencies on the public.emp_view materialized
view is retrieved.
REFRESH
You can use the
Example
REFRESH
stored procedure to update a list of views separated by commas (,) or all the specified
views in the DBMS_UTILITY.UNCL_ARRAY
table.
- You can use the following syntax to specify 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);
- You can use the following syntax to specify views in the
DBMS_UTILITY.UNCL_ARRAY
table: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);
Parameter | Description |
---|---|
list | Specifies the name of a materialized view or a list of materialized view names separated by commas (,). |
tab | Specifies the name of a materialized view. |
method | Specifies the update method to be applied to the specified view. C is the only supported method. This method performs a complete update of the view. |
rollback_seg | This parameter is used for compatibility and can be ignored. Default value: NULL. |
push_deferred_rpc | This parameter is used for compatibility and can be ignored. Default value: TRUE. |
refresh_after_errors | This parameter is used for compatibility and can be ignored. Default value: FALSE. |
purge_option | This parameter is used for compatibility and can be ignored. Default value: 1. |
parallelism | This parameter is used for compatibility and can be ignored. Default value: 0. |
heap_size | This parameter is used for compatibility and can be ignored. Default value: 0. |
atomic_refresh | This parameter is used for compatibility and can be ignored. Default value: TRUE. |
nested | This parameter is used for compatibility and can be ignored. Default value: FALSE. |
EXEC DBMS_MVIEW.REFRESH(list => 'public.emp_view', method => 'C');
REFRESH_ALL_M VIEWS
You can use the
Example
REFRESH_ALL_MVIEWS
stored procedure to update materialized views that are not updated after the table
or view on which the views depend is updated. Syntax: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);
ParametersParameter | Description |
---|---|
number_of_failures | Specifies the number of failed updates in an update operation. |
method | Specifies the update method to be applied to the specified view. C is the only supported method. This method performs a complete update of the view. |
rollback_seg | This parameter is used for compatibility and can be ignored. Default value: NULL. |
refresh_after_errors | This parameter is used for compatibility and can be ignored. Default value: FALSE. |
atomic_refresh | This parameter is used for compatibility and can be ignored. Default value: TRUE. |
DECLARE
errors INTEGER;
BEGIN
DBMS_MVIEW.REFRESH_ALL_MVIEWS(errors, method => 'C');
END;
After the update is complete, the errors variable contains the number of failed updates.
REFRESH_DEPENDENT
You can use the
Example
REFRESH_DEPENDENT
stored procedure to update all materialized views. You can specify a list of views
separated by commas (,) or specify view names in the DBMS_UTILITY.UNCL_ARRAY
table.
- You can use the following syntax to specify a comma-separated list of view names:
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);
- You can use the following syntax to specify view names in the
DBMS_UTILITY.UNCL_ARRAY
table: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);
Parameter | Description |
---|---|
number_of_failures | Specifies the number of failed updates in an update operation. |
list | Specifies the name of a materialized view or a list of materialized view names separated by commas (,). |
tab | Specifies the name of a materialized view. |
method | Specifies the update method to be applied to the specified view. C is the only supported method. This method performs a complete update of the view. |
rollback_seg | This parameter is used for compatibility and can be ignored. Default value: NULL. |
refresh_after_errors | This parameter is used for compatibility and can be ignored. Default value: FALSE. |
atomic_refresh | This parameter is used for compatibility and can be ignored. Default value: TRUE. |
nested | This parameter is used for compatibility and can be ignored. Default value: FALSE. |
DECLARE
errors INTEGER;
BEGIN
DBMS_MVIEW.REFRESH_DEPENDENT(errors, list => 'public.emp_view', method => 'C');
END;
After the update is complete, the errors variable contains the number of failed updates.