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. The list and tab parameters are mutually exclusive. ParametersTable 17-5 MAKE Procedure ParametersParameter 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 [schema_name.]materialized_view_name. If the schema is not specified, then the refresh group owner is the default.
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 [schema_name.]materialized_view_name. If the schema is not specified, then the refresh group owner is the default.
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 NEXT_DAY(SYSDATE+1, "MONDAY") as your interval, and if your next_date evaluates to Monday, then Oracle refreshes the materialized views every Monday. 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.
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 PUSH with purge set to aggressive to reduce the queue.
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.
begin dbms_refresh.refresh('"XX"."XXXX_MV"'); commit; end;
A materialized view containing only joins can be defined to be refreshed ON COMMIT or ON DEMAND. If it is ON COMMIT, the refresh is performed at commit time of the transaction that does DML on the materialized view's detail table. OR you can try EXECUTE dbms_snapshot.refresh(‘ <view name>’, ‘<option>’)
dbms_refresh.make(name => 'group2',
list => 'MV_TMP_STAFF',
next_date => sysdate,
interval => 'sysdate+1',
implicit_destroy => true);
end;
list => 'MV_TMP_STAFF',
next_date => sysdate,
interval => 'sysdate+1',
implicit_destroy => true
这里面有几个参数啊,每个参数什么意思,比如我想每天执行两次,中午十二点和晚上七点。谢谢你了!
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. The list and tab parameters are mutually exclusive.
ParametersTable 17-5 MAKE Procedure ParametersParameter 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 [schema_name.]materialized_view_name. If the schema is not specified, then the refresh group owner is the default.
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 [schema_name.]materialized_view_name. If the schema is not specified, then the refresh group owner is the default.
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 NEXT_DAY(SYSDATE+1, "MONDAY") as your interval, and if your next_date evaluates to Monday, then Oracle refreshes the materialized views every Monday. 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.
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 PUSH with purge set to aggressive to reduce the queue.
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.
BEGIN
SYS.DBMS_REFRESH.MAKE(
'PERA.MAT_RESH',
'PERA.MAT_VIEW',
to_date('2009-09-02 15:41:04', 'yyyy/MM/dd hh24:mi:ss'),
'SYSDATE+3/(60*24)',
FALSE,
TRUE,
0,
NULL,
TRUE,
FALSE
);
END;
/
但是视图并没有定时刷新,那位高手能告诉我是什么问题吗?
dbms_refresh.refresh('"XX"."XXXX_MV"');
commit;
end;
it is ON COMMIT, the refresh is performed at commit time of the transaction that does DML on the
materialized view's detail table.
OR you can try
EXECUTE dbms_snapshot.refresh(‘ <view name>’, ‘<option>’)