Sysaux tablespace purging Find the biggest occupants: select * from v$sysaux_occupants where space_usage_kbytes>1048576 order by 6; Or the segments: col owner for a20 col segment_name for a30 select owner, segment_type, segment_name, bytes/1048576 from dba_segments where tablespace_name='SYSAUX' and bytes>104857600 order by 4; ---------------------------------------------------------------------------------------------------- If the top occupants are 'SM/OPTSTAT' and 'SM/ADVISOR', you could follow How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1) probably changing v_min_age from 30 to 20 or 10 days: set serveroutput on DECLARE v_oldest INTEGER := 730; -- Two years v_increment INTEGER := 50; v_cur_age INTEGER; v_min_age INTEGER := 20; -- Retain one month. Change to 20 days BEGIN v_cur_age := v_oldest; WHILE v_cur_age >= v_min_age LOOP dbms_sqltune.set_tuning_task_parameter(task_name => 'AUTO_STATS_ADVISOR_TASK', parameter => 'EXECUTION_DAYS_TO_EXPIRE', value => v_cur_age); prvt_advisor.delete_expired_tasks; v_cur_age := v_cur_age - v_increment; END LOOP; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Execution halted with error number ' || sqlerrm); END; / followed by moving table WRI$_ADV_OBJECTS and rebuilding its unusable indexes. The advantage of this code in the document is better than exec prvt_advisor.delete_expired_tasks because the latter will almost always throw ORA-1555 ("snapshot too old"). However, even the small step cleanup using the above PL/SQL code is not ideal; it will generates lots of redo and archive log and it runs many hours. The best and cleanest solution is a simple truncate. Follow Step 3 of SYSAUX Tablespace Grows Rapidly After Upgrading Database to 12.2.0.1 or Above Due To Statistics Advisor (Doc ID 2305512.1) to simply truncate table WRI$_ADV_OBJECTS.[note] ---------------------------------------------------------------------------------------------------- If the top occupant is heatmap, follow HEATMAP Segment Size Is Large In SYSAUX Even When Heatmap=Off (Doc ID 2024036.1) ---------------------------------------------------------------------------------------------------- If it's unified auditing records, follow How To Purge The UNIFIED AUDIT TRAIL (Doc ID 1582627.1) ______________________ [note] Ref: SR 3-33612933171 in spite of Can I Truncate Objects in SYSAUX if they are Taking up too much Space? (Doc ID 1928156.1)