Monday, April 25, 2016

Changing AWR snapshot retention

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);



No comments:

Post a Comment

Featured Post

Apply Patch 22191577 latest GI PSU to RAC and DB homes using Opatch auto or manual steps

Patch 22191577: GRID INFRASTRUCTURE PATCH SET UPDATE 11.2.0.4.160119 (JAN2016) Unzip the patch 22191577 Unzip latest Opatch Version in or...