Purge AWR snapshots data in Oracle

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.