SYSAUX Tablespace. What is it for, and why it is growing out of space.

==========================
What is SYSAUX Tablespace.
==========================
In Oracle 11, Some database components were moved to SYSAUX tablespace from SYSTEM Tablespace.
It is known as "auxiliary tablespace to the SYSTEM tablespace"
The advantage in this configuration is, that if the SYSAUX tablespace becomes unavailable, core database functionality will remain operational. 
The database features that use the SYSAUX tablespace could fail, or function with limited capability.

==========================
Per Oracle Documentation:
==========================
The largest portion of the SYSAUX tablespace is occupied by the Automatic Workload Repository (AWR). The space consumed by the AWR is determined by several factors, including the number of active sessions in the system at any given time, the snapshot interval, and the historical data retention period. A typical system with an average of 10 concurrent active sessions may require approximately 200 to 300 MB of space for its AWR data.
Another major occupant of the SYSAUX tablespace is the embedded Enterprise Manager (EM) repository.

The following table provides guidelines on sizing the SYSAUX tablespace based on the system configuration and expected load.

Parameter/Recommendation                      Small Medium    Large
Number of CPUs                                2      8         32
Number of concurrently active sessions        10     20        100
Number of user objects: tables and indexes    500    5,000     50,000
Estimated default SYSAUX size at steady state 500MB  2GB       5GB

==========================
Monitoring Occupants of the SYSAUX Tablespace
==========================
Overtime, the size of SYSAUX Tablespace should be stable.
To check what uis eating up SYSAUX Tablespace size:

V$SYSAUX_OCCUPANTS View
The list of registered occupants of the SYSAUX tablespace can be viewed in  V$SYSAUX_OCCUPANTS.

utlsyxsz.sql 
Connected as a DBA user, run the script ${ORACLE_HOME}/rdbms/admin/utlsyxsz.sql to get the current usage of the SYSAUX tablespace.


==========================
How to free up space in SYSAUX Tablespace

==========================
Staring point:
Tablespace SYSAUX is full 91%
About 5.5Gb from 6Gb are used

TABLESPACE_NAME DBA_FREE_SPACE_MB USED_SPACE_MB MAX_SPACE_MB   FREE_PCT ADD_
--------------- ----------------- ------------- ------------ ---------- ----

SYSAUX                        554          5446         6000          9 Y

Checking for top segments:

Top Segments in SYSAUX

OWNER    TABLESPACE_NAME SEGMENT_NAME                          USED_MB

-------- --------------- ------------------------------ --------------

SYS      SYSAUX          WRH$_ACTIVE_SESSION_HISTORY              1992

SYS      SYSAUX          WRH$_SYSMETRIC_HISTORY                    336

SYS      SYSAUX          WRH$_ACTIVE_SESSION_HISTORY_PK            311

SYS      SYSAUX          WRH$_EVENT_HISTOGRAM_PK                   256

SYS      SYSAUX          WRH$_SYSMETRIC_HISTORY_INDEX              248

SYS      SYSAUX          WRH$_EVENT_HISTOGRAM                      248

SYS      SYSAUX          WRH$_LATCH                                144

SYS      SYSAUX          WRH$_SQLSTAT                              127

SYS      SYSAUX          WRH$_LATCH_MISSES_SUMMARY                 118

SYS      SYSAUX          WRH$_LATCH_MISSES_SUMMARY_PK              110

Seems that the retention for WRH$ is not working.

Check for current retention setup:

SQL> SELECT RETENTION from  WRM$_WR_CONTROL;

RETENTION

-------------------------

+00008 00:00:00.0

SQL> SELECT MIN(SNAP_ID) FROM WRM$_SNAPSHOT;

MIN(SNAP_ID)

------------

       57488

SQL> SELECT TO_CHAR(BEGIN_INTERVAL_TIME,'YYYYMMDD hh24:mi:ss') 

FROM WRM$_SNAPSHOT WHERE SNAP_ID = 57488;

TO_CHAR(BEGIN_INTERVAL_TIME,'YYYYMMDDHH24:MI:SS')

--------------------------------------------------

20190118 16:00:17

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE

------------------

14-AUG-19

So, instead of keeping only 8 last days, the data kept is much longer.

To purge old data manually:

1. Get the last snap_id to keep

SELECT snap_id AS MAX_SNAP_ID, TO_CHAR(BEGIN_INTERVAL_TIME,'YYYYMMDDHH24:MI:SS') MAX_INTERVAL_TIME

FROM wrm$_snapshot 

WHERE snap_id =

 (SELECT MAX(snap_id) snap_id

    FROM WRM$_SNAPSHOT

   WHERE begin_interval_time < (SYSDATE - (SELECT retention FROM WRM$_WR_CONTROL ))

 );

MAX_SNAP_ID MAX_INTERVAL_TIME

----------- -------------------

      62272 2019080607:00:29

Check for possible Baselines, that would require older data:

select baseline_name,creation_time

 from dba_hist_baseline;

BASELINE_NAME            CREATION_TIME

------------------------ ------------------

SYSTEM_MOVING_WINDOW     26-OCT-08

SYSTEM_MOVING_WINDOW is a default one.

Should there have been other baselines, they would have to be removed.

Remove the obsolete data

BEGIN

  DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range(low_snap_id=>57488, high_snap_id=>62271);

END;

/

This step would take quite a long time. up to several hours.

Then reorganize tables and indexes by running generated SQLs:

SET HEADING OFF

SET PAGESIZE 0

SET LINESIZE 200

SET VERIFY OFF

spool rebuild_tables.sql

SELECT 'ALTER TABLE '||segment_name||' MOVE TABLESPACE SYSAUX ;' from dba_segments where tablespace_name= 'SYSAUX' and segment_name like 'WRH$_%' and segment_type = 'TABLE'   order by segment_name

spool off

spool rebuild_indexes.sql

SELECT 'ALTER INDEX '||segment_name||' REBUILD ONLINE;' from dba_segments where tablespace_name= 'SYSAUX' and segment_name like 'WRH$_%' and segment_type='INDEX' order by segment_name;

spool off