DanBrother的部落格

-- Modify Snapshot Retention:

select retention from dba_hist_wr_control;

RETENTION
---------------------------------------------------------------------------
+00008 00:00:00.0

-- Change retention to 30 days (43200 minutes)
exec dbms_workload_repository.modify_snapshot_settings(retention=>43200);

RETENTION
---------------------------------------------------------------------------
+00030 00:00:00.0

When extending the retention day, the size of the SYSAUX tablespace needs also be expanded.  The size of the SYSAUX tablespace can be estimated through utlsyxsz.sql (Utility script for SYSAUX Size) located in $ORACLE_HOME/rdbms/admin directory


SQL> @?/rdbms/admin/utlsyxsz.sql

-- Modify Moving_Window_Size
Moving window baseline is used to calculate metrics for the adaptive thrsholds.
The window is a view of the AWR data within the retention period.
The default value of the window is the same as the default AWR retention period (8days)

select moving_window_size
from dba_hist_baseline
where baseline_type = 'MOVING_WINDOW';

MOVING_WINDOW_SIZE
------------------
                 8

exec dbms_workload_repository.modify_baseline_window_size(window_size=>30);

select moving_window_size from dba_hist_baseline
where baseline_type = 'MOVING_WINDOW';

MOVING_WINDOW_SIZE
------------------
                30