Purge Old Snapshots From SYSAUX Tablespace

Sometimes you find that SYSAUX tablespace has grown in space so you need to free space from the tablespace.

one of the things you may need to remove from SYSAUX tablespace is the old Snapshots.

Using the following PL/SQL code you will be able to purge the no longer needed Snapshots From SYSAUX Tablespace

DECLARE
V_DBID NUMBER;
V_MIN_SNAP NUMBER;
V_MAX_SNAP NUMBER;
BEGIN
SELECT DBID INTO V_DBID FROM V$DATABASE;

SELECT MIN (SNAP_ID), MAX (SNAP_ID) - 20
INTO V_MIN_SNAP, V_MAX_SNAP
FROM DBA_HIST_SNAPSHOT;

DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (LOW_SNAP_ID => V_MIN_SNAP,
HIGH_SNAP_ID => V_MAX_SNAP,
DBID => V_DBID);
END;
/