Oracle : Change AWR Retention

Check existing retention and current snapshot interval.

set linesize 300
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;

Below query is similar like above but it gives retention in Days:

set linesize 300
col snap_interval for a30
col retention for a30
col TOPNSQL for a20
select * from dba_hist_wr_control;

Change Retention and snapshot interval :

— Example exec dbms_workload_repository.modify_snapshot_settings(retention=>144000, interval=>10, dbid=>1234567890);
— retention=>144000 –> for 100 days (100*24*60)
— interval -> 10 –> 10 Min

 exec dbms_workload_repository.modify_snapshot_settings(retention=>&Provide_in_min_for_retention, interval=>&interval, dbid=>&dbid); 

Run above queries again to validate.