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;

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

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


Leave a Reply

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

You are commenting using your 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