Oracle® Database Advanced Replication Management API Reference 10g Release 2 (10.2) Part Number B14227-01 |
|
|
View PDF |
DBMS_REFRESH
enables you to create groups of materialized views that can be refreshed together to a transactionally consistent point in time.
This chapter contains this topic:
Table 17-1 DBMS_REFRESH Package Subprograms
Subprogram | Description |
---|---|
"ADD Procedure" |
Adds materialized views to a refresh group. |
"CHANGE Procedure" |
Changes the refresh interval for a refresh group. |
"DESTROY Procedure" |
Removes all of the materialized views from a refresh group and deletes the refresh group. |
"MAKE Procedure" |
Specifies the members of a refresh group and the time interval used to determine when the members of this group should be refreshed. |
"REFRESH Procedure" |
Manually refreshes a refresh group. |
"SUBTRACT Procedure" |
Removes materialized views from a refresh group. |
This procedure adds materialized views to a refresh group.
See Also:
Step 6, "Add objects to the refresh group.", and Oracle Database Advanced Replication for more informationSyntax
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 17-2 ADD Procedures Parameters
Parameter | Description |
---|---|
name |
Name of the refresh group to which you want to add members, specified as [ schema_name .] refresh_group_name . If the schema is not specified, then the current user is the default. |
list |
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 |
tab |
Instead of a comma-delimited list, you can supply a PL/SQL index-by table of type DBMS_UTILITY .UNCL_ARRAY , where each element is the name of a materialized view. The first materialized view should be in position 1. The last position must be NULL .
Each materialized view is specified as |
lax |
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 lax flag to true to succeed. Oracle then automatically removes the materialized view from the other refresh group and updates its refresh interval to be that of its new group. Otherwise, the call to ADD generates an error message. |
This procedure changes the refresh interval for a refresh group.
See Also:
Oracle Database Advanced Replication for more information about refresh groupsSyntax
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 17-3 CHANGE Procedures Parameters
Parameter | Description |
---|---|
name |
Name of the refresh group for which you want to alter the refresh interval. |
next_date |
Next date that you want a refresh to occur. By default, this date remains unchanged. |
interval |
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, you should select an interval that is greater than the time it takes to perform a refresh. By default, the interval remains unchanged. |
implicit_destroy |
Allows you to reset the value of the implicit_destroy flag. If this flag is set, then Oracle automatically deletes the group if it no longer contains any members. By default, this flag remains unchanged. |
rollback_seg |
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 NULL , including the quotes. Specifying NULL without quotes indicates that you do not want to change the rollback segment currently being used. |
push_deferred_rpc |
Used by updatable materialized views only. Set this parameter to true if you want to push changes from the materialized view to its associated master table or master materialized view before refreshing the materialized view. Otherwise, these changes might appear to be temporarily lost. By default, this flag remains unchanged. |
refresh_after_errors |
Used by updatable materialized views only. Set this parameter to true if you want the refresh to proceed even if there are outstanding conflicts logged in the DEFERROR view for the materialized view's master table or master materialized view. By default, this flag remains unchanged. |
purge_option |
If you are using the parallel propagation mechanism (that is, parallelism is set to 1 or greater), then:
In most cases, lazy purge is the optimal setting. Set purge to aggressive to trim back the queue if multiple master replication groups are pushed to different target sites, and updates to one or more replication groups are infrequent and infrequently pushed. If all replication groups are infrequently updated and pushed, then set purge to do not purge and occasionally execute |
parallelism |
0 specifies serial propagation.
n > 1 specifies parallel propagation with n parallel processes. 1 specifies parallel propagation using only one parallel process. |
heap_size |
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 procedure removes all of the materialized views from a refresh group and delete the refresh group.
See Also:
Oracle Database Advanced Replication for more information refresh groupsSyntax
DBMS_REFRESH.DESTROY ( name IN VARCHAR2);
Parameters
Table 17-4 DESTROY Procedure Parameters
Parameter | Description |
---|---|
name |
Name of the refresh group that you want to destroy. |
This procedure specifies the members of a refresh group and the time interval used to determine when the members of this group should be refreshed.
See Also:
Step 4, "Create the refresh group.", and Oracle Database Advanced Replication for more informationSyntax
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);
Note:
This procedure is overloaded. Thelist
and tab
parameters are mutually exclusive.Parameters
Table 17-5 MAKE Procedure Parameters
Parameter | Description |
---|---|
name |
Unique name used to identify the refresh group, specified as [ schema_name .] refresh_group_name . If the schema is not specified, then the current user is the default. Refresh groups must follow the same naming conventions as tables. |
list |
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 |
tab |
Instead of a comma-delimited list, you can supply a PL/SQL index-by table of names of materialized views that you want to refresh using the datatype DBMS_UTILITY .UNCL_ARRAY . If the table contains the names of n materialized views, then the first materialized view should be in position 1 and the n + 1 position should be set to NULL .
Each materialized view is specified as |
next_date |
Next date that you want a refresh to occur. |
interval |
Function used to calculate the next time to refresh the materialized views in the group. This field is used with the next_date value.
For example, if you specify |
implicit_destroy |
Set this to true if you want to delete the refresh group automatically when it no longer contains any members. Oracle checks this flag only when you call the SUBTRACT procedure. That is, setting this flag still enables you to create an empty refresh group. |
lax |
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 true to succeed. Oracle then automatically removes the materialized view from the other refresh group and updates its refresh interval to be that of its new group. Otherwise, the call to MAKE generates an error message. |
job |
Needed by the Import utility. Use the default value, 0. |
rollback_seg |
Name of the rollback segment to use while refreshing materialized views. The default, NULL , uses the default rollback segment. |
push_deferred_rpc |
Used by updatable materialized views only. Use the default value, true , if you want to push changes from the materialized view to its associated master table or master materialized view before refreshing the materialized view. Otherwise, these changes might appear to be temporarily lost. |
refresh_after_errors |
Used by updatable materialized views only. Set this to 0 if you want the refresh to proceed even if there are outstanding conflicts logged in the DEFERROR view for the materialized view's master table or master materialized view. |
purge_option |
If you are using the parallel propagation mechanism (in other words, parallelism is set to 1 or greater), then 0 = do not purge; 1 = lazy (default); 2 = aggressive. In most cases, lazy purge is the optimal setting.
Set purge to aggressive to trim back the queue if multiple master replication groups are pushed to different target sites, and updates to one or more replication groups are infrequent and infrequently pushed. If all replication groups are infrequently updated and pushed, then set purge to do not purge and occasionally execute |
parallelism |
0 specifies serial propagation.
n > 1 specifies parallel propagation with n parallel processes. 1 specifies parallel propagation using only one parallel process. |
heap_size |
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 procedure manually refreshes a refresh group.
See Also:
Oracle Database Advanced Replication for more information about refresh groupsSyntax
DBMS_REFRESH.REFRESH ( name IN VARCHAR2);
Parameter
Table 17-6 REFRESH Procedure Parameter
Parameter | Description |
---|---|
name |
Name of the refresh group that you want to refresh manually. |
This procedure removes materialized views from a refresh group.
See Also:
Oracle Database Advanced Replication for more information about refresh groupsSyntax
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 17-7 SUBTRACT Procedure Parameters
Parameter | Description |
---|---|
name |
Name of the refresh group from which you want to remove members. |
list |
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. |
tab |
Instead of a comma-delimited list, you can supply a PL/SQL index-by table of names of materialized views that you want to refresh using the datatype DBMS_UTILITY .UNCL_ARRAY . If the table contains the names of n materialized views, then the first materialized view should be in position 1 and the n + 1 position should be set to NULL . |
lax |
Set this to false if you want Oracle to generate an error message if the materialized view you are attempting to remove is not a member of the refresh group. |