Using dbms_workload_repository.modify_snapshot_settings one can modify retention, interval and topnsql.
-- get the dbid which is needed to passs to dbms_workload_repository.modify_snapshot_settings SQL> select dbid from v$database; DBID ---------- 1992878807 -- retention=>value in minutes so (45 days * 24 (hours per day) * 60 minutes per hour = 64800), max value can be set by passing a value of 0 which means forever -- internal=>60min (snap at this interval), a value of 0 will turn off AWR -- topnsql - top N sql size, specify value of NULL will keep the current setting SQL> exec dbms_workload_repository.modify_snapshot_settings(retention=>64800, interval=>60, topnsql=>100, dbid=>1992878807); PL/SQL procedure successfully completed. -- shows retention and interval after it was modified SQL> 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 64800 100 -- Change snapshot interval to 30mins SQL> EXEC dbms_workload_repository.modify_snapshot_settings(interval=>30); PL/SQL procedure successfully completed.
[…] Password for ExpirationExtract all tablespaces DDL « Substitution Variable Examples How to change AWR retention, interval, topnsql? […]
thank you for sharing this information.