133 DBMS_REFRESH
The DBMS_REFRESH
package enables you to create groups of materialized views that can be refreshed together to a transactionally consistent point in time. These groups are called refresh groups.
This chapter contains the following topics:
133.1 DBMS_REFRESH Overview
When it is important for materialized views to be transactionally consistent with each other, you can organize them into refresh groups.
By refreshing the refresh group, you can ensure that the data in all of the materialized views in the refresh group correspond to the same transactionally consistent point in time. A materialized view in a refresh group still can be refreshed individually, but doing so nullifies the benefits of the refresh group because refreshing the materialized view individually does not refresh the other materialized views in the refresh group
133.2 DBMS_REFRESH Security Model
Users must have the EXECUTE
privilege to run the procedures of DBMS_REFRESH
package.
133.3 Summary of DBMS_REFRESH Subprograms
This table lists the DBMS_REFRESH
subprograms and briefly describes them.
Table 133-1 DBMS_REFRESH Package Subprograms
Subprogram | Description |
---|---|
Adds materialized views to a refresh group |
|
Changes the refresh interval for a refresh group |
|
Removes all of the materialized views from a refresh group and deletes the refresh group |
|
Specifies the members of a refresh group and the time interval used to determine when to refresh the members of this group |
|
Manually refreshes a refresh group |
|
Removes materialized views from a refresh group |
133.3.1 ADD Procedure
This procedure adds materialized views to a refresh group.
Syntax
DBMS_REFRESH.ADD ( name IN VARCHAR2, { list IN VARCHAR2, | tab IN DBMS_UTILITY.UNCL_ARRAY, } lax IN BOOLEAN := FALSE);
Note:
This procedure is overloaded. Thelist
and tab
parameters are mutually exclusive.
Parameters
Table 133-2 ADD Procedure Parameters
Parameter | Description |
---|---|
|
Name of the refresh group to which you want to add members, specified as |
|
Comma-delimited list of materialized views that you want to add to the refresh group. Synonyms are not supported. Each materialized view is specified as |
|
Instead of a comma-delimited list, you can supply a PL/SQL associative array of type Each materialized view is specified as |
|
A materialized view can belong to only one refresh group at a time. If you are moving a materialized view from one group to another, then you must set the |
133.3.2 CHANGE Procedure
This procedure changes the refresh interval for a refresh group.
Syntax
DBMS_REFRESH.CHANGE ( name IN VARCHAR2, next_date IN DATE := NULL, interval IN VARCHAR2 := NULL, implicit_destroy IN BOOLEAN := NULL, rollback_seg IN VARCHAR2 := NULL, push_deferred_rpc IN BOOLEAN := NULL, refresh_after_errors IN BOOLEAN := NULL, purge_option IN BINARY_INTEGER := NULL, parallelism IN BINARY_INTEGER := NULL, heap_size IN BINARY_INTEGER := NULL);
Parameters
Table 133-3 CHANGE Procedure Parameters
Parameter | Description |
---|---|
|
Name of the refresh group for which you want to alter the refresh interval. |
|
Next date that you want a refresh to occur. By default, this date remains unchanged. |
|
Function used to calculate the next time to refresh the materialized views in the refresh group. This interval is evaluated immediately before the refresh. Thus, select an interval that is greater than the time it takes to perform a refresh. By default, the interval remains unchanged. |
|
Allows you to reset the value of the |
|
Allows you to change the rollback segment used. By default, the rollback segment remains unchanged. To reset this parameter to use the default rollback segment, specify |
|
Starting with Oracle Database 12c Release 2 (12.2), this parameter is ignored. |
|
Starting with Oracle Database 12c Release 2 (12.2), this parameter is ignored. |
|
Starting with Oracle Database 12c Release 2 (12.2), this parameter is ignored. |
|
|
|
Maximum number of transactions to be examined simultaneously for parallel propagation scheduling. Oracle automatically calculates the default setting for optimal performance. Note: Do not set this parameter unless directed to do so by Oracle Support Services. |
133.3.3 DESTROY Procedure
This procedure removes all of the materialized views from a refresh group and delete the refresh group.
Syntax
DBMS_REFRESH.DESTROY ( name IN VARCHAR2);
Parameters
Table 133-4 DESTROY Procedure Parameters
Parameter | Description |
---|---|
|
Name of the refresh group that you want to destroy. |
133.3.4 MAKE Procedure
This procedure specifies the members of a refresh group and the time interval used to determine when to refresh the members of this group.
Syntax
DBMS_REFRESH.MAKE ( name IN VARCHAR2 { list IN VARCHAR2, | tab IN DBMS_UTILITY.UNCL_ARRAY,} next_date IN DATE, interval IN VARCHAR2, implicit_destroy IN BOOLEAN := FALSE, lax IN BOOLEAN := FALSE, job IN BINARY_INTEGER := 0, rollback_seg IN VARCHAR2 := NULL, push_deferred_rpc IN BOOLEAN := TRUE, refresh_after_errors IN BOOLEAN := FALSE purge_option IN BINARY_INTEGER := NULL, parallelism IN BINARY_INTEGER := NULL, heap_size IN BINARY_INTEGER := NULL job_name IN VARCHAR2 := NULL, auto_commit IN BOOLEAN := NULL);
Note:
This procedure is overloaded. Thelist
and tab
parameters are mutually exclusive.
Parameters
Table 133-5 MAKE Procedure Parameters
Parameter | Description |
---|---|
|
Unique name used to identify the refresh group, specified as |
|
Comma-delimited list of materialized views that you want to refresh. Synonyms are not supported. These materialized views can be located in different schemas and have different master tables or master materialized views. However, all of the listed materialized views must be in your current database. Each materialized view is specified as |
|
Instead of a comma-delimited list, you can supply a PL/SQL associative array of names of materialized views that you want to refresh using the data type Each materialized view is specified as |
|
Next date that you want a refresh to occur. |
|
Function used to calculate the next time to refresh the materialized views in the group. This field is used with the For example, if you specify |
|
Set this to |
|
A materialized view can belong to only one refresh group at a time. If you are moving a materialized view from an existing group to a new refresh group, then you must set this to |
|
Needed by the Import utility. Use the default value, 0. |
|
Name of the rollback segment to use while refreshing materialized views. The default, |
|
Starting with Oracle Database 12c Release 2 (12.2), this parameter is ignored. |
|
Starting with Oracle Database 12c Release 2 (12.2), this parameter is ignored. |
|
Starting with Oracle Database 12c Release 2 (12.2), this parameter is ignored. |
|
|
|
Maximum number of transactions to be examined simultaneously for parallel propagation scheduling. Oracle automatically calculates the default setting for optimal performance. Note: Do not set this parameter unless directed to do so by Oracle Support Services. |
|
This parameter is needed by the import utility. User should use the default value, |
|
Supported values are
The default value is |
Usage Notes
Import utility and export utility need CREATE JOB
privilege if DBMS_SCHEDULER
jobs are used.
133.3.5 REFRESH Procedure
This procedure manually refreshes a refresh group.
Syntax
DBMS_REFRESH.REFRESH ( name IN VARCHAR2);
Parameters
Table 133-6 REFRESH Procedure Parameters
Parameter | Description |
---|---|
|
Name of the refresh group that you want to refresh manually. |
133.3.6 SUBTRACT Procedure
This procedure removes materialized views from a refresh group.
Syntax
DBMS_REFRESH.SUBTRACT ( name IN VARCHAR2, { list IN VARCHAR2, | tab IN DBMS_UTILITY.UNCL_ARRAY, } lax IN BOOLEAN := FALSE);
Note:
This procedure is overloaded. Thelist
and tab
parameters are mutually exclusive.
Parameters
Table 133-7 SUBTRACT Procedure Parameters
Parameter | Description |
---|---|
|
Name of the refresh group from which you want to remove members, specified as |
|
Comma-delimited list of materialized views that you want to remove from the refresh group. (Synonyms are not supported.) These materialized views can be located in different schemas and have different master tables or master materialized views. However, all of the listed materialized views must be in your current database. Each materialized view is specified as |
|
Instead of a comma-delimited list, you can supply a PL/SQL associative array of type Each materialized view is specified as |
|
Set this to |