Purge AWR Snapshots Manually from Oracle Database

Query to find AWR snapshot retention period using below query.

SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION
---------------------------
			 31

Query to check the snapshot interval.

SQL> SELECT SNAP_INTERVAL, RETENTION FROM DBA_HIST_WR_CONTROL;

SNAP_INTERVAL								    RETENTION
--------------------------------------------------------------------------- ---------------------------------------------------------------------------
+00000 01:00:00.0							    +00008 00:00:00.0

advertisements

 

SQL to purge AWR snapshots which are older than x days. In below example 30 days. This will purge the snapshots which are older than 30 days.

SQL> exec dbms_stats.purge_stats(SYSDATE-30);

PL/SQL procedure successfully completed.

SQL to purge awr snapshots with snap id range.

SQL> EXECUTE dbms_workload_repository.drop_snapshot_range(low_snap_id =>27 , high_snap_id =>30);

PL/SQL procedure successfully completed.