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:
Table 1. 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 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);
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 (,).
Example
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 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);
Parameters
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.
Example
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. 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);
Parameters
Parameter 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.
Example
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 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);
Parameters
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.
Example
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.