To check what the current retention is
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,
topnsql
from dba_hist_wr_control;
SNAPSHOT_INTERVAL RETENTION_INTERVAL TOPNSQL
----------------- ------------------ ----------
60 20160 DEFAULT
Current retention is snapshot every 60 minutes and retention days=20160 minutes
which means 14 days (24*14*60)
If we want to change retention to 10 days
which means (24*10*60) = 14400
execute dbms_workload_repository.modify_snapshot_settings ( interval => 60, retention => 14400);
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,
topnsql
from dba_hist_wr_control;
SNAPSHOT_INTERVAL RETENTION_INTERVAL TOPNSQL
----------------- ------------------ ----------
60 20160 DEFAULT
Current retention is snapshot every 60 minutes and retention days=20160 minutes
which means 14 days (24*14*60)
If we want to change retention to 10 days
which means (24*10*60) = 14400
execute dbms_workload_repository.modify_snapshot_settings ( interval => 60, retention => 14400);
No comments:
Post a Comment