Sysaux tablespace is too big

 today I received my report alert regarding one of Oracle database tablespace SYSAUX is over 80% (I created automated job using SSIS package to check tablespace usage daily)

following steps to resolve it:

 /*

 2021.07.08

 Documentation steps for troubleshooting Sysaux tablespace is huge --85% full

 */

 

---1. see what uses the most

select occupant_name,occupant_desc, space_usage_kbytes/1024 MB

from v$sysaux_occupants

where space_usage_kbytes > 0

order by space_usage_kbytes desc;

---find it is SM/Advisor Server Manageability - Advisor Framework 25GB

--now look for top 10 big objects

 

select * from (

               select bytes/1024/1024 MB, blocks, s.SEGMENT_NAME, s.partition_name, s.segment_type, s.tablespace_name

                 from dba_segments s

                where owner='SYS'

             order by bytes desc

)

where rownum <=10

;

---find it is WRI$_ADV_OBJECTS having 13GB and 1.7 million blocks

--now check how many objects in dba_advisor_objects

 select task_name, count(*) cnt from dba_advisor_objects group by task_name order by cnt desc;

 ----find it is AUTO_STATS_ADVISOR_TASK with 119 million objects

  

---or Check the number of  rows in WRI$_ADV_OBJECTS for Auto Stats Advisor Task  , same as 119 million rows

 select count(*) from wri$_adv_objects where task_id=(select distinct id from wri$_adv_tasks where name='AUTO_STATS_ADVISOR_TASK');

 

--- now create table as  from WRI$_ADV_OBJECTS to keep the rows except those for AUTO_STATS_ADVISOR_TASK  

 create table wri$_adv_objects_new as select * from wri$_adv_objects where task_id !=(select distinct id from wri$_adv_tasks where name='AUTO_STATS_ADVISOR_TASK'); 

---check new table rows that excludes AUTO_STATS_ADVISOR_TASK objects

 select count(*) from wri$_adv_objects_new; ---only 24k

 ---now  Truncate the table 

  truncate table wri$_adv_objects;

  

  

----   insert rows back from WRI$_ADV_OBJECTS_NEW for other advisor objects  

  insert /*+ APPEND */ into wri$_adv_objects select * from wri$_adv_objects_new;

  commit;

  drop table wri$_adv_objects_new;

----reorganize indexes

alter index wri$_adv_objects_idx_01 rebuild;

 alter index wri$_adv_objects_pk rebuild;

 

 ---check tablespace size

 

SELECT Sysdate as DateCollected, "TABLESPACE_NAME", "PERCENT_USED", "PCT_USED", "PERCENT_USED_MAXBYTES", "PCT_USED_MAXBYTES", "MAX_DATAFILES", "ALLOCATED", "USED", "UNUSED_ALLOCATED", "DATAFILES" FROM(

select * from (

SELECT a.tablespace_name,

'SQLDEV:GAUGE:0:100:0:0:'||nvl(ROUND(((c.bytes-nvl(b.bytes,0))/c.bytes)*100,2),0) percent_used, -- orig

ROUND(((c.bytes-nvl(b.bytes,0))/c.bytes)*100,2) PCT_USED, -- orig

'SQLDEV:GAUGE:0:100:0:0:'||nvl(ROUND(((c.bytes-nvl(b.bytes,0))/c.maxbytes)*100,2),0) percent_used_maxbytes,

ROUND(((c.bytes-nvl(b.bytes,0))/c.maxbytes)*100,2) PCT_USED_maxbytes,

round(c.bytes/1024/1024,2) allocated,

round(c.bytes/1024/1024-nvl(b.bytes,0)/1024/1024,2) used,

round(c.maxbytes/1024/1024,2) max_datafiles,

round(nvl(b.bytes,0)/1024/1024,2) unused_allocated,

c.datafiles

FROM dba_tablespaces a,

( SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space GROUP BY tablespace_name ) b,

( select count(1) datafiles, SUM(bytes) bytes, SUM(DECODE(autoextensible, 'YES', maxbytes, bytes)) maxbytes, tablespace_name from dba_data_files GROUP BY tablespace_name ) c

WHERE b.tablespace_name (+) = a.tablespace_name

AND c.tablespace_name (+) = a.tablespace_name

ORDER BY nvl(((c.bytes-nvl(b.bytes,0))/c.maxbytes),0) DESC

) --sub1 order by 1 asc

);

---now tablespace only 9% full