DBMS_MVIEW - Developer Guide| Alibaba Cloud Documentation Center

You can use the stored procedures in the DBMS_MVIEW package to manage and update materialized views and their dependencies.

PolarDB for Oracle supports the following DBMS_MVIEW stored procedures:

Table 1. 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 for Oracle is only partially implemented when compared to Oracle's version. PolarDB for Oracle 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.