Is your SYSAUX tablespace way too large?

Is your SYSAUX tablespace way too large?

If you think your SYSAUX tablespace is too big, it may be that AWR is leaving behind stuff that it should have deleted. Doc ID 2099998.1 on Oracle Support describes the problem. The database deletes snapshots that are older than the configured retention. But sometimes, if the process gets interrupted, it may leave some orphan data behind.

Ensure you are correctly licensed before you start querying the AWR tables; it requires the diagnostic management pack (AFAIK, but I have not worked for years on an enterprise database without all the management packs licensed). You can use the script $ORACLE_HOME/rdbms/admin/awrinfo.sql to check how much space the various components of AWR occupy.

The article on Oracle Support lists three DELETE statements and six ALTER TABLE statements to fix the problem. The DELETE statements remove rows that do not belong to any snapshot

DELETE FROM sys.WRH$_LATCH a
WHERE NOT EXISTS
(SELECT 1
FROM sys.wrm$_snapshot b
WHERE b.snap_id = a.snap_id
AND dbid=(SELECT dbid FROM v$database)
AND b.dbid = a.dbid
AND b.instance_number = a.instance_number);
 
 
DELETE FROM sys.WRH$_SYSSTAT a
WHERE NOT EXISTS
(SELECT 1
FROM sys.wrm$_snapshot b
WHERE b.snap_id = a.snap_id
AND dbid=(SELECT dbid FROM v$database)
AND b.dbid = a.dbid
AND b.instance_number = a.instance_number)
-- and rownum < 1e7
;
 
DELETE FROM sys.WRH$_PARAMETER a
WHERE NOT EXISTS
(SELECT 1
FROM sys.wrm$_snapshot b
WHERE b.snap_id = a.snap_id
AND dbid=(SELECT dbid FROM v$database)
AND b.dbid = a.dbid
AND b.instance_number = a.instance_number)
-- and rownum < 1e7
;
 
COMMIT;

On one occasion, I had to remove it in batches; that’s why and rownum < 1e7 is commented out.

To reclaim the space from the tables back to the tablespace, you must enable row movement and shrink the tables:

ALTER TABLE sys.WRH$_LATCH ENABLE ROW MOVEMENT;
ALTER TABLE sys.WRH$_SYSSTAT ENABLE ROW MOVEMENT;
ALTER TABLE sys.WRH$_PARAMETER ENABLE ROW MOVEMENT;
ALTER TABLE sys.WRH$_LATCH SHRINK SPACE CASCADE;
ALTER TABLE sys.WRH$_SYSSTAT SHRINK SPACE CASCADE;
ALTER TABLE sys.WRH$_PARAMETER SHRINK SPACE CASCADE;

From a pluggable database, you need to shrink the tables like this:

begin
   dbms_pdb.exec_as_oracle_script('ALTER TABLE sys.WRH$_LATCH SHRINK SPACE CASCADE');
   dbms_pdb.exec_as_oracle_script('ALTER TABLE sys.WRH$_SYSSTAT SHRINK SPACE CASCADE');
   dbms_pdb.exec_as_oracle_script('ALTER TABLE sys.WRH$_PARAMETER SHRINK SPACE CASCADE');
end;
/

Lastly, if you want to reduce the size of the tablespace, you can reduce the data file size with the ALTER DATABASE command. You will probably not be able to reclaim all of it unless you start moving the tables around. Also, don’t forget that your time may be more valuable than disk space ;-)