Received an alert from one of the databases that SYSAUX tablespace is nearing its allocated 24GB space. 24GB is too much for SYSAUX, especially on a development database… so need to make sure the contents are reviewed before adding more space…
Oracle10g+ versions have a view V$SYSAUX_OCCUPANTS, showing what components are in SYSAUX tablespace and how much space is used. It also shows the procedure to use if you decide to move this component from SYSAUX to another tablespace.
SQL> col OCCUPANT_NAME format a20 SQL> select occupant_name, space_usage_kbytes from v$sysaux_occupants where space_usage_kbytes > 10240 order by space_usage_kbytes desc; OCCUPANT_NAME SPACE_USAGE_KBYTES -------------------- ------------------ SM/AWR 2875776 LOGMNR 573184 SM/ADVISOR 448384 XDB 177216 SDO 79936 STREAMS 76928 AO 49536 XSOQHIST 49536 XSAMD 31872 SM/OTHER 17984 TEXT 14720 11 rows selected. SQL>
So, it really does not add up anywhere near 24GB, the largest component is AWR and is only occupying less than 3GB… What else is in SYSAUX, that does no belong to the standard Oracle components?
SQL> col SEGMENT_NAME format a25 SQL> col segment_type format a10 SQL> col bytes format "999,999,999,999" SQL> select segment_name, bytes from dba_segments where tablespace_name = 'SYSAUX' and bytes > 1048576000 order by bytes; SEGMENT_NAME BYTES ------------------------- ---------------- SYS_LOB0002516712C00008$$ 20,408,434,688 1 row selected. SQL>
It’s a LOB segment, let us find out which table…
SQL> select table_name, column_name 2 from dba_lobs 3 where segment_name = 'SYS_LOB0002516712C00008$$'; TABLE_NAME ------------------------------ COLUMN_NAME --------------------------------------------- STREAMS$_APPLY_SPILL_MSGS_PART MESSAGE
Aaaha! We recently enabled Change Data Capture feature on this database, and it is using STREAMS… Now, need to see what’s causing it to grow and how to fix…!
Also, shows that V$SYSAUX_OCCUPANTS do not consider the LOB segments, when calculating space occupied 🙁