MODIFY_SNAPSHOT_SETTINGS Procedure
This procedure controls two aspects of snapshot generation. The INTERVAL setting affects how often snapshots are automatically captured while the RETENTION setting affects how long snapshots are retained in the Workload Repository.
Syntax:
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS((
retention IN NUMBER DEFAULT NULL,
interval IN NUMBER DEFAULT NULL,
dbid IN NUMBER DEFAULT NULL);
Parameters:
Parameter Description
retention:
The new retention time (in minutes). The specified value must be in the range of 1 day to 100 years. If ZERO is specified, the maximum value of 100 years will be used. If NULL is specified, the old value for retention is preserved.
interval:
The new interval setting between each snapshot, in units of minutes. The specified value must be in the range between 10 minutes to 1 year. If ZERO is specified, the maximum value of 1 year will be used. If NULL is specified, the current value is preserved.
dbid:
The database id (default to local DBID.
Examples:
This example changes the interval setting to one hour and the retention setting to two weeks for the local database:
execute dbms_workload_repository.modify_snapshot_settings(interval => 60,retention => 20160);
If you query the DBA_HIST_WR_CONTROL table after this procedure is executed, you will see the changes to these settings.
select
extract( day from snap_interval) *24*60+
extract( hour from snap_interval) *60+
extract( minute from snap_interval ) "Snapshot Interval",
extract( day from retention) *24*60+
extract( hour from retention) *60+
extract( minute from retention ) "Retention Interval"
from dba_hist_wr_control;
(or)
select SNAP_INTERVAL,RETENTION from DBA_HIST_WR_CONTROL;
(from Oracle 11g)
Ex:
Interval > 60 min
Keep time > 30 days.
exec dbms_workload_repository.modify_snapshot_settings (interval => 60,retention => 43200);
This procedure controls two aspects of snapshot generation. The INTERVAL setting affects how often snapshots are automatically captured while the RETENTION setting affects how long snapshots are retained in the Workload Repository.
Syntax:
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS((
retention IN NUMBER DEFAULT NULL,
interval IN NUMBER DEFAULT NULL,
dbid IN NUMBER DEFAULT NULL);
Parameters:
Parameter Description
retention:
The new retention time (in minutes). The specified value must be in the range of 1 day to 100 years. If ZERO is specified, the maximum value of 100 years will be used. If NULL is specified, the old value for retention is preserved.
interval:
The new interval setting between each snapshot, in units of minutes. The specified value must be in the range between 10 minutes to 1 year. If ZERO is specified, the maximum value of 1 year will be used. If NULL is specified, the current value is preserved.
dbid:
The database id (default to local DBID.
Examples:
This example changes the interval setting to one hour and the retention setting to two weeks for the local database:
execute dbms_workload_repository.modify_snapshot_settings(interval => 60,retention => 20160);
If you query the DBA_HIST_WR_CONTROL table after this procedure is executed, you will see the changes to these settings.
select
extract( day from snap_interval) *24*60+
extract( hour from snap_interval) *60+
extract( minute from snap_interval ) "Snapshot Interval",
extract( day from retention) *24*60+
extract( hour from retention) *60+
extract( minute from retention ) "Retention Interval"
from dba_hist_wr_control;
(or)
select SNAP_INTERVAL,RETENTION from DBA_HIST_WR_CONTROL;
(from Oracle 11g)
Ex:
Interval > 60 min
Keep time > 30 days.
exec dbms_workload_repository.modify_snapshot_settings (interval => 60,retention => 43200);
