How To See The Retention Time For AWR

With this query we can see the time of retention of data for the AWR, the value is rounded to days, also we can see the execution interval snapshots;

SELECT
   EXTRACT( DAY FROM snap_interval) * 1440 +
   EXTRACT( HOUR FROM snap_interval) * 60 +
   EXTRACT( MINUTE FROM snap_interval ) AS "Snapshot (Minutes)",
   EXTRACT( DAY FROM retention) AS "Retention (Days)"
FROM 
   dba_hist_wr_control;

Attention, if value is zero it means that the data will be stored forever.

You can change your retention time with the following command, eg for one day retention (value in minutes);

sql> exec dbms_workload_repository.modify_snapshot_settings(retention => 1440);

HTH – Antonio NAVARRO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s