Tag Archives: Check retention period of AWR

Purge AWR snapshots data in Oracle

Purge AWR snapshots data in Oracle

Check the retention period of AWR


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 PM 27-NOV-19 PM                                                                                                                                    
     22466 27-NOV-19 PM 27-NOV-19 PM                                                                                                                                    
     22467 27-NOV-19 PM 27-NOV-19 PM                                                                                                                                    
     22468 27-NOV-19 PM 27-NOV-19 PM                                                                                                                                    
     22469 27-NOV-19 PM 27-NOV-19 PM                                                                                                                                    
     22470 27-NOV-19 PM 28-NOV-19 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);

More information Generate report of AWRINFO.SQL located in %ORACLE_HOME%\RDBMS\ADMIN folder.

Sample is attached: AWRINFO REPORT