How to manually purge AWR snapshots in Oracle database

 

DROP_SNAPSHOT_RANGE procedure is used to drop a range of snapshots from AWR repository. You can pass below parameters to the DROP_SNAPSHOT_RANGE procedure. low_snap_id :- Low snapshot id of snapshots to drop. high_snap_id :- High snapshot id of snapshots to drop. dbid :- Database id (defaults to local DBID). #Execute below query to check existing AWR snapshots. SQL> set lines 300 pages 3000 SQL> col BEGIN_INTERVAL_TIME for a25 SQL> col END_INTERVAL_TIME for a25 SQL> select INSTANCE_NUMBER INST_NUM ,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME,SNAP_ID from DBA_HIST_SNAPSHOT order by 4; INST_NUM BEGIN_INTERVAL_TIME       END_INTERVAL_TIME         SNAP_ID -------- ------------------------- ------------------------- -------        1 01-JUN-24 03.12.29.000 PM 01-JUN-24 03.23.26.310 PM     581        1 01-JUN-24 03.23.26.310 PM 01-JUN-24 04.00.29.533 PM     582        1 01-JUN-24 04.00.29.533 PM 01-JUN-24 04.30.32.964 PM     583        1 01-JUN-24 04.30.32.964 PM 01-JUN-24 05.00.36.315 PM     584        1 01-JUN-24 05.00.36.315 PM 01-JUN-24 05.30.39.754 PM     585        1 01-JUN-24 05.52.45.000 PM 01-JUN-24 05.55.48.142 PM     586        1 01-JUN-24 05.55.48.142 PM 01-JUN-24 05.57.09.107 PM     587        1 03-JUN-24 11.53.03.000 PM 04-JUN-24 12.03.11.702 AM     588        1 04-JUN-24 12.03.11.702 AM 04-JUN-24 12.30.12.781 AM     589        1 04-JUN-24 12.30.12.781 AM 04-JUN-24 01.00.17.737 AM     590        1 04-JUN-24 01.00.17.737 AM 04-JUN-24 01.15.20.161 AM     591        1 04-JUN-24 01.15.20.161 AM 04-JUN-24 01.30.22.586 AM     592        1 04-JUN-24 01.30.22.586 AM 04-JUN-24 01.45.24.842 AM     593        1 04-JUN-24 01.45.24.842 AM 04-JUN-24 02.00.27.408 AM     594        1 04-JUN-24 02.00.27.408 AM 04-JUN-24 02.15.29.786 AM     595        1 04-JUN-24 02.15.29.786 AM 04-JUN-24 02.30.32.210 AM     596        1 04-JUN-24 02.30.32.210 AM 04-JUN-24 02.45.34.613 AM     597        1 04-JUN-24 02.45.34.613 AM 04-JUN-24 03.00.37.226 AM     598        1 04-JUN-24 03.00.37.226 AM 04-JUN-24 03.15.39.912 AM     599        1 04-JUN-24 03.15.39.912 AM 04-JUN-24 03.30.42.561 AM     600        1 04-JUN-24 03.30.42.561 AM 04-JUN-24 05.54.36.269 AM     601        1 04-JUN-24 05.54.36.269 AM 04-JUN-24 08.28.53.944 PM     602        1 04-JUN-24 08.28.53.944 PM 04-JUN-24 08.45.56.389 PM     603        1 04-JUN-24 08.45.56.389 PM 04-JUN-24 09.00.57.282 PM     604        1 04-JUN-24 09.00.57.282 PM 04-JUN-24 09.15.59.420 PM     605        1 04-JUN-24 09.15.59.420 PM 04-JUN-24 09.30.01.484 PM     606        1 04-JUN-24 09.30.01.484 PM 04-JUN-24 09.45.03.403 PM     607        1 04-JUN-24 09.45.03.403 PM 04-JUN-24 10.12.27.283 PM     608        1 04-JUN-24 10.12.27.283 PM 04-JUN-24 10.31.04.135 PM     609        1 04-JUN-24 10.31.04.135 PM 04-JUN-24 10.45.27.722 PM     610        1 04-JUN-24 10.45.27.722 PM 04-JUN-24 11.09.41.368 PM     611        1 04-JUN-24 11.09.41.368 PM 04-JUN-24 11.15.45.285 PM     612        1 04-JUN-24 11.15.45.285 PM 04-JUN-24 11.30.47.309 PM     613        1 04-JUN-24 11.30.47.309 PM 04-JUN-24 11.47.11.962 PM     614        1 04-JUN-24 11.47.11.962 PM 05-JUN-24 01.34.33.053 AM     615        1 05-JUN-24 01.34.33.053 AM 05-JUN-24 02.48.28.454 AM     616        1 05-JUN-24 02.48.28.454 AM 05-JUN-24 03.00.30.243 AM     617        1 05-JUN-24 03.00.30.243 AM 05-JUN-24 03.15.32.603 AM     618        1 05-JUN-24 03.15.32.603 AM 05-JUN-24 03.30.34.996 AM     619        1 05-JUN-24 03.30.34.996 AM 05-JUN-24 03.45.39.712 AM     620        1 05-JUN-24 03.45.39.712 AM 05-JUN-24 06.55.34.946 AM     621        1 05-JUN-24 06.55.34.946 AM 05-JUN-24 07.19.47.852 AM     622        1 09-JUN-24 10.09.24.000 AM 09-JUN-24 10.20.16.998 AM     623        1 09-JUN-24 10.20.16.998 AM 09-JUN-24 10.30.18.310 AM     624        1 09-JUN-24 10.30.18.310 AM 09-JUN-24 10.45.19.884 AM     625        1 09-JUN-24 10.45.19.884 AM 09-JUN-24 10.49.03.717 AM     626        1 09-JUN-24 10.49.03.717 AM 09-JUN-24 10.50.29.639 AM     627        1 09-JUN-24 10.50.29.639 AM 09-JUN-24 10.51.20.076 AM     628        1 09-JUN-24 10.51.20.076 AM 09-JUN-24 10.51.56.307 AM     629        1 09-JUN-24 10.51.56.307 AM 09-JUN-24 10.52.23.040 AM     630        1 09-JUN-24 10.52.23.040 AM 09-JUN-24 10.54.30.038 AM     631        1 09-JUN-24 10.54.30.038 AM 09-JUN-24 10.55.00.484 AM     632        1 09-JUN-24 10.55.00.484 AM 09-JUN-24 10.55.29.008 AM     633        1 09-JUN-24 10.55.29.008 AM 09-JUN-24 10.56.59.750 AM     634        1 09-JUN-24 10.56.59.750 AM 09-JUN-24 10.57.35.224 AM     635        1 09-JUN-24 10.57.35.224 AM 09-JUN-24 11.15.23.225 AM     636        1 09-JUN-24 11.15.23.225 AM 09-JUN-24 11.30.24.962 AM     637        1 09-JUN-24 11.30.24.962 AM 09-JUN-24 11.45.27.124 AM     638 58 rows selected. #You cannot pass single value in DROP_SNAPSHOT_RANGE procedure. You will face below error message. SQL> select INSTANCE_NUMBER INST_NUM ,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME,SNAP_ID from DBA_HIST_SNAPSHOT where snap_id=581 order by 4; INST_NUM BEGIN_INTERVAL_TIME       END_INTERVAL_TIME         SNAP_ID -------- ------------------------- ------------------------- -------        1 01-JUN-24 03.12.29.000 PM 01-JUN-24 03.23.26.310 PM     581 SQL> EXECUTE DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(581); BEGIN DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(581); END;       * ERROR at line 1: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'DROP_SNAPSHOT_RANGE' ORA-06550: line 1, column 7: PL/SQL: Statement ignored #Execute below query to drop AWR snapshot range 582 to 585. SQL> select INSTANCE_NUMBER INST_NUM ,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME,SNAP_ID from DBA_HIST_SNAPSHOT where snap_id between 582 and 585 order by 4; INST_NUM BEGIN_INTERVAL_TIME       END_INTERVAL_TIME         SNAP_ID -------- ------------------------- ------------------------- -------        1 01-JUN-24 03.23.26.310 PM 01-JUN-24 04.00.29.533 PM     582        1 01-JUN-24 04.00.29.533 PM 01-JUN-24 04.30.32.964 PM     583        1 01-JUN-24 04.30.32.964 PM 01-JUN-24 05.00.36.315 PM     584        1 01-JUN-24 05.00.36.315 PM 01-JUN-24 05.30.39.754 PM     585 SQL> EXECUTE DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(582, 585); PL/SQL procedure successfully completed. SQL> select INSTANCE_NUMBER INST_NUM ,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME,SNAP_ID from DBA_HIST_SNAPSHOT where snap_id between 582 and 585 order by 4; no rows selected #Execute below query to drop AWR snapshot range 586 to 599. SQL> select INSTANCE_NUMBER INST_NUM ,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME,SNAP_ID from DBA_HIST_SNAPSHOT where snap_id between 586 and 599 order by 4; INST_NUM BEGIN_INTERVAL_TIME       END_INTERVAL_TIME         SNAP_ID -------- ------------------------- ------------------------- -------        1 01-JUN-24 05.52.45.000 PM 01-JUN-24 05.55.48.142 PM     586        1 01-JUN-24 05.55.48.142 PM 01-JUN-24 05.57.09.107 PM     587        1 03-JUN-24 11.53.03.000 PM 04-JUN-24 12.03.11.702 AM     588        1 04-JUN-24 12.03.11.702 AM 04-JUN-24 12.30.12.781 AM     589        1 04-JUN-24 12.30.12.781 AM 04-JUN-24 01.00.17.737 AM     590        1 04-JUN-24 01.00.17.737 AM 04-JUN-24 01.15.20.161 AM     591        1 04-JUN-24 01.15.20.161 AM 04-JUN-24 01.30.22.586 AM     592        1 04-JUN-24 01.30.22.586 AM 04-JUN-24 01.45.24.842 AM     593        1 04-JUN-24 01.45.24.842 AM 04-JUN-24 02.00.27.408 AM     594        1 04-JUN-24 02.00.27.408 AM 04-JUN-24 02.15.29.786 AM     595        1 04-JUN-24 02.15.29.786 AM 04-JUN-24 02.30.32.210 AM     596        1 04-JUN-24 02.30.32.210 AM 04-JUN-24 02.45.34.613 AM     597        1 04-JUN-24 02.45.34.613 AM 04-JUN-24 03.00.37.226 AM     598        1 04-JUN-24 03.00.37.226 AM 04-JUN-24 03.15.39.912 AM     599 14 rows selected. SQL> EXECUTE DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(low_snap_id=>586, high_snap_id=>599); PL/SQL procedure successfully completed. SQL> select INSTANCE_NUMBER INST_NUM ,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME,SNAP_ID from DBA_HIST_SNAPSHOT where snap_id between 586 and 599 order by 4; no rows selected #You cannot pass only dbid parameter in DROP_SNAPSHOT_RANGE procedure. You will face below error message. SQL> select INSTANCE_NUMBER INST_NUM ,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME,SNAP_ID from DBA_HIST_SNAPSHOT where DBID=2447257321 order by 4; INST_NUM BEGIN_INTERVAL_TIME       END_INTERVAL_TIME         SNAP_ID -------- ------------------------- ------------------------- -------        1 01-JUN-24 03.12.29.000 PM 01-JUN-24 03.23.26.310 PM     581        1 04-JUN-24 03.15.39.912 AM 04-JUN-24 03.30.42.561 AM     600        1 04-JUN-24 03.30.42.561 AM 04-JUN-24 05.54.36.269 AM     601        1 04-JUN-24 05.54.36.269 AM 04-JUN-24 08.28.53.944 PM     602        1 04-JUN-24 08.28.53.944 PM 04-JUN-24 08.45.56.389 PM     603        1 04-JUN-24 08.45.56.389 PM 04-JUN-24 09.00.57.282 PM     604        1 04-JUN-24 09.00.57.282 PM 04-JUN-24 09.15.59.420 PM     605        1 04-JUN-24 09.15.59.420 PM 04-JUN-24 09.30.01.484 PM     606        1 04-JUN-24 09.30.01.484 PM 04-JUN-24 09.45.03.403 PM     607        1 04-JUN-24 09.45.03.403 PM 04-JUN-24 10.12.27.283 PM     608        1 04-JUN-24 10.12.27.283 PM 04-JUN-24 10.31.04.135 PM     609        1 04-JUN-24 10.31.04.135 PM 04-JUN-24 10.45.27.722 PM     610        1 04-JUN-24 10.45.27.722 PM 04-JUN-24 11.09.41.368 PM     611        1 04-JUN-24 11.09.41.368 PM 04-JUN-24 11.15.45.285 PM     612        1 04-JUN-24 11.15.45.285 PM 04-JUN-24 11.30.47.309 PM     613        1 04-JUN-24 11.30.47.309 PM 04-JUN-24 11.47.11.962 PM     614        1 04-JUN-24 11.47.11.962 PM 05-JUN-24 01.34.33.053 AM     615        1 05-JUN-24 01.34.33.053 AM 05-JUN-24 02.48.28.454 AM     616        1 05-JUN-24 02.48.28.454 AM 05-JUN-24 03.00.30.243 AM     617        1 05-JUN-24 03.00.30.243 AM 05-JUN-24 03.15.32.603 AM     618        1 05-JUN-24 03.15.32.603 AM 05-JUN-24 03.30.34.996 AM     619        1 05-JUN-24 03.30.34.996 AM 05-JUN-24 03.45.39.712 AM     620        1 05-JUN-24 03.45.39.712 AM 05-JUN-24 06.55.34.946 AM     621        1 05-JUN-24 06.55.34.946 AM 05-JUN-24 07.19.47.852 AM     622        1 09-JUN-24 10.09.24.000 AM 09-JUN-24 10.20.16.998 AM     623        1 09-JUN-24 10.20.16.998 AM 09-JUN-24 10.30.18.310 AM     624        1 09-JUN-24 10.30.18.310 AM 09-JUN-24 10.45.19.884 AM     625        1 09-JUN-24 10.45.19.884 AM 09-JUN-24 10.49.03.717 AM     626        1 09-JUN-24 10.49.03.717 AM 09-JUN-24 10.50.29.639 AM     627        1 09-JUN-24 10.50.29.639 AM 09-JUN-24 10.51.20.076 AM     628        1 09-JUN-24 10.51.20.076 AM 09-JUN-24 10.51.56.307 AM     629        1 09-JUN-24 10.51.56.307 AM 09-JUN-24 10.52.23.040 AM     630        1 09-JUN-24 10.52.23.040 AM 09-JUN-24 10.54.30.038 AM     631        1 09-JUN-24 10.54.30.038 AM 09-JUN-24 10.55.00.484 AM     632        1 09-JUN-24 10.55.00.484 AM 09-JUN-24 10.55.29.008 AM     633        1 09-JUN-24 10.55.29.008 AM 09-JUN-24 10.56.59.750 AM     634        1 09-JUN-24 10.56.59.750 AM 09-JUN-24 10.57.35.224 AM     635        1 09-JUN-24 10.57.35.224 AM 09-JUN-24 11.15.23.225 AM     636        1 09-JUN-24 11.15.23.225 AM 09-JUN-24 11.30.24.962 AM     637        1 09-JUN-24 11.30.24.962 AM 09-JUN-24 11.45.27.124 AM     638        1 09-JUN-24 11.45.27.124 AM 09-JUN-24 12.00.29.260 PM     639 41 rows selected. SQL> EXECUTE DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(dbid=>2447257321); BEGIN DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(dbid=>2447257321); END;       * ERROR at line 1: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'DROP_SNAPSHOT_RANGE' ORA-06550: line 1, column 7: PL/SQL: Statement ignored #You cannot pass all three parameters (low_snap_id | high_snap_id | dbid) together to drop range of snapshots.   SQL> EXECUTE DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(low_snap_id=>600, high_snap_id=>610, dbid=>2447257321); PL/SQL procedure successfully completed. SQL> select INSTANCE_NUMBER INST_NUM ,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME,SNAP_ID from DBA_HIST_SNAPSHOT where DBID=2447257321 order by 4; INST_NUM BEGIN_INTERVAL_TIME       END_INTERVAL_TIME         SNAP_ID -------- ------------------------- ------------------------- -------        1 01-JUN-24 03.12.29.000 PM 01-JUN-24 03.23.26.310 PM     581        1 04-JUN-24 10.45.27.722 PM 04-JUN-24 11.09.41.368 PM     611        1 04-JUN-24 11.09.41.368 PM 04-JUN-24 11.15.45.285 PM     612        1 04-JUN-24 11.15.45.285 PM 04-JUN-24 11.30.47.309 PM     613        1 04-JUN-24 11.30.47.309 PM 04-JUN-24 11.47.11.962 PM     614        1 04-JUN-24 11.47.11.962 PM 05-JUN-24 01.34.33.053 AM     615        1 05-JUN-24 01.34.33.053 AM 05-JUN-24 02.48.28.454 AM     616        1 05-JUN-24 02.48.28.454 AM 05-JUN-24 03.00.30.243 AM     617        1 05-JUN-24 03.00.30.243 AM 05-JUN-24 03.15.32.603 AM     618        1 05-JUN-24 03.15.32.603 AM 05-JUN-24 03.30.34.996 AM     619        1 05-JUN-24 03.30.34.996 AM 05-JUN-24 03.45.39.712 AM     620        1 05-JUN-24 03.45.39.712 AM 05-JUN-24 06.55.34.946 AM     621        1 05-JUN-24 06.55.34.946 AM 05-JUN-24 07.19.47.852 AM     622        1 09-JUN-24 10.09.24.000 AM 09-JUN-24 10.20.16.998 AM     623        1 09-JUN-24 10.20.16.998 AM 09-JUN-24 10.30.18.310 AM     624        1 09-JUN-24 10.30.18.310 AM 09-JUN-24 10.45.19.884 AM     625        1 09-JUN-24 10.45.19.884 AM 09-JUN-24 10.49.03.717 AM     626        1 09-JUN-24 10.49.03.717 AM 09-JUN-24 10.50.29.639 AM     627        1 09-JUN-24 10.50.29.639 AM 09-JUN-24 10.51.20.076 AM     628        1 09-JUN-24 10.51.20.076 AM 09-JUN-24 10.51.56.307 AM     629        1 09-JUN-24 10.51.56.307 AM 09-JUN-24 10.52.23.040 AM     630        1 09-JUN-24 10.52.23.040 AM 09-JUN-24 10.54.30.038 AM     631        1 09-JUN-24 10.54.30.038 AM 09-JUN-24 10.55.00.484 AM     632        1 09-JUN-24 10.55.00.484 AM 09-JUN-24 10.55.29.008 AM     633        1 09-JUN-24 10.55.29.008 AM 09-JUN-24 10.56.59.750 AM     634        1 09-JUN-24 10.56.59.750 AM 09-JUN-24 10.57.35.224 AM     635        1 09-JUN-24 10.57.35.224 AM 09-JUN-24 11.15.23.225 AM     636        1 09-JUN-24 11.15.23.225 AM 09-JUN-24 11.30.24.962 AM     637        1 09-JUN-24 11.30.24.962 AM 09-JUN-24 11.45.27.124 AM     638        1 09-JUN-24 11.45.27.124 AM 09-JUN-24 12.00.29.260 PM     639