You can use the stored procedures in the DBMS_MVIEW package to manage and update materialized views and their dependencies. PolarDB-O supports the following DBMS_MVIEW stored procedures:
| Stored procedure | Return type | Description |
|---|---|---|
| GET_MV_DEPENDENCIES(list VARCHAR2, deplist VARCHAR2); | N/A | The GET_MV_DEPENDENCIES stored procedure can be used to retrieve 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); | N/A | The variation of the REFRESH stored procedure can be used to update 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 | The variation of the REFRESH stored procedure can be used to update 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); | N/A | The REFRESH_ALL_MVIEWS stored procedure can be used to update 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 | The variation of the REFRESH_DEPENDENT stored procedure can be used to update 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 | The variation of the REFRESH_DEPENDENT stored procedure can be used to update all views that are dependent on the views listed in a table of dbms_utility.uncl_array values. |
The DBMS_MVIEW package in PolarDB-O is only partially implemented when compared to Oracle's version. PolarDB-O only supports the stored procedures that are listed in the preceding table.
GET_MV_DEPENDENCIES
After a materialized view is named, you can use the GET_MV_DEPENDENCIES stored procedure to retrieve a list of items that are dependent on the specified view. The following code describes the syntax of the GET_MV_DEPENDENCIES stored procedure:
GET_MV_DEPENDENCIES(
list IN VARCHAR2,
deplist OUT VARCHAR2);Parameters
| Parameter | 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 (,).
Note The data type of the deplist parameter is VARCHAR2.
|
Examples
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 REFRESH stored procedure to update a list of views separated by commas (,), or all views specified in a table of DBMS_UTILITY.UNCL_ARRAY values. The REFRESH stored procedure has two forms of syntax. When you specify a list of views separated by commas (,), you can use the first form of syntax:
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);The second form of syntax is used to specify views in a table of DBMS_UTILITY.UNCL_ARRAY values.
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);Parameters
| Parameter | Description |
|---|---|
| list | The data type of the list parameter is VARCHAR2. The parameter specifies the name of a materialized view, or a list of materialized view names separated by commas (,). The names must be schema-qualified. |
| tab | The parameter specifies the names of materialized views in a table of DBMS_UTILITY.UNCL_ARRAY values. |
| method | The data type of the method parameter is VARCHAR2. The parameter specifies the update method to be applied to the specified view. C is the only supported method, which is used to perform a complete update of the view. |
| rollback_seg | rollback_seg is used for compatibility and can be ignored. The default value is NULL. |
| push_deferred rpc | push_deferred_rpc is used for compatibility and can be ignored. The default value is TRUE. |
| refresh_after_errors | refresh_after_errors is used for compatibility and can be ignored. The default value is FALSE. |
| purge_option | purge_option is used for compatibility and can be ignored. The default value is 1. |
| parallelism | parallelism is used for compatibility and can be ignored. The default value is 0. |
| heap_size IN NUMBER DEFAULT 0, | heap_size is used for compatibility and can be ignored. The default value is 0. |
| atomic refresh | atomic_refresh is used for compatibility and can be ignored. The default value is TRUE. |
| nested | nested is used for compatibility and can be ignored. The default value is FALSE. |
Examples
The following example uses DBMS_MVIEW.REFRESH to update the materialized view named public.emp_view:
EXEC DBMS_MVIEW.REFRESH(list => 'public.emp_view', method => 'C');REFRESH_ALL_M VIEWS
You can use the 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. The following code describes the syntax of the REFRESH_ALL_MVIEWS stored procedure:
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);Parameters
| Parameter | Description |
|---|---|
| number_of_failures | The data type of the number_of_failures parameter is BINARY_INTEGER. The parameter specifies the number of failures that occur during the update operation. |
| method | The data type of the method parameter is VARCHAR2. The parameter specifies the update method to be applied to the specified view. C is the only supported method, which is used to perform a complete update of the view. |
| rollback_seg | rollback_seg is used for compatibility and can be ignored. The default value is NULL. |
| refresh_after_errors | refresh_after_errors is used for compatibility and can be ignored. The default value is FALSE. |
| atomic refresh | atomic_refresh is used for compatibility and can be ignored. The default value is TRUE. |
Examples
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 failures.
REFRESH_DEPENDENT
You can use the REFRESH_DEPENDENT stored procedure to update all materialized views that are dependent on the views specified in the call to the stored procedure. You can specify a list of views separated by commas (,) or specify views in a table of DBMS_UTILITY.UNCL_ARRAY values.
The following syntax of the stored procedure is used to update all materialized views that are dependent on the views specified in a comma-separated list:
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);The following syntax of the stored procedure is used to update all materialized views that are dependent on the views specified in a table of DBMS_UTILITY.UNCL_ARRAY values.
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);Parameters
| Parameter | Description |
|---|---|
| number_of_failures | The data type of the number_of_failures parameter is BINARY_INTEGER. The parameter specifies the number of failures that occur during the update operation. |
| list | The data type of the list parameter is VARCHAR2. The parameter specifies the name of materialized view, or a list of materialized view names separated by commas (,). The names must be schema-qualified. |
| tab | Specifies the names of materialized views in a table of DBMS_UTILITY.UNCL_ARRAY values. |
| method | The data type of the method parameter is VARCHAR2. The parameter specifies the update method to be applied to the specified view. C is the only supported method, which is used to perform a complete update of the view. |
| rollback_seg | rollback_seg is used for compatibility and can be ignored. The default value is NULL. |
| refresh_after_errors | refresh_after_errors is used for compatibility and can be ignored. The default value is FALSE. |
| atomic refresh | atomic_refresh is used for compatibility and can be ignored. The default value is TRUE. |
| nested | nested is used for compatibility and can be ignored. The default value is FALSE. |
Examples
The following example describes a complete update on all materialized views that depend on a materialized view named emp_view. emp_view resides in the public schema.
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 failures.