Purge AWR snapshots data in Oracle
Check the retention period of AWR
SELECT SNAP_INTERVAL, RETENTION FROM DBA_HIST_WR_CONTROL;
Check the list of AWR Snaphots present in DB
set line 999 pages 999
col begin_interval_time for a25
col end_interval_time for a25
SELECT snap_id, begin_interval_time, end_interval_time
FROM sys.wrm$_snapshot ORDER BY snap_id
SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME
---------- ------------------------- -------------------------
22465 27-NOV-19 06.00.05.470 PM 27-NOV-19 07.00.12.445 PM
22466 27-NOV-19 07.00.12.445 PM 27-NOV-19 08.00.19.655 PM
22467 27-NOV-19 08.00.19.655 PM 27-NOV-19 09.00.26.595 PM
22468 27-NOV-19 09.00.26.595 PM 27-NOV-19 10.00.34.525 PM
22469 27-NOV-19 10.00.34.525 PM 27-NOV-19 11.00.41.572 PM
22470 27-NOV-19 11.00.41.572 PM 28-NOV-19 12.00.48.527 AM
Purging the data of AWR by choosing the snap id
EXECUTE dbms_workload_repository.drop_snapshot_range(low_snap_id =>22465 , high_snap_id =>22470);
Note:
More information Generate report of AWRINFO.SQL located in %ORACLE_HOME%\RDBMS\ADMIN folder.
Sample is attached: AWRINFO REPORT
