SYSAUX Tablespace Growth - Review and Fix

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  :-(

Tablespace Free Space and Max Size

Earlier, provided a small script to find the maximum growable size of a tablespace... Here is little more comprehensive script that shows more tablespace space info. Current size, growable size based on the autoextensible data files, free space, biggest chunk...All size is in MB.


SELECT a.tablespace_name TSNAME, 
        ROUND(SUM(a.growable)/1048576) growable, 
        ROUND(SUM(a.tots)/1048576) Tot_Size,
        ROUND(SUM(a.sumb)/1048576) Tot_Free,
        ROUND(SUM(a.sumb)*100/sum(a.tots)) Pct_Free,
        ROUND(SUM(a.largest)/1048576) Large_Ext
FROM   (SELECt tablespace_name, 0 tots, SUM(bytes) sumb,
               MAX(bytes) largest, 0 growable
        FROM   dba_free_space a
        GROUP BY tablespace_name
        UNION
        SELECT tablespace_name, SUM(bytes) tots, 0, 0,  SUM (GREATEST (bytes, DECODE (autoextensible, 'YES', maxbytes, 0))) growable
        FROM   dba_data_files
        GROUP BY tablespace_name
        UNION
        SELECT tablespace_name, SUM(bytes) tots, 0, 0,  SUM (GREATEST (bytes, DECODE (autoextensible, 'YES', maxbytes, 0))) growable
        FROM   dba_temp_files
        GROUP BY tablespace_name) a, dba_tablespaces b
WHERE b.tablespace_name = a.tablespace_name
GROUP BY rollup(a.tablespace_name)
ORDER by a.tablespace_name

Enjoy!

Query - Tablespace Max Size

I sometimes see concerned emails from developers that they are running out of space in tablespace and the DBA need to do something immediately. The DBA will start wondering we have monitoring in place, then how come it did not catch an out of space situation, but a user/developer did...

For you developer/user, if you are wondering if your database/tablespace is running out of space, you may need to check if the tablespace has autoextensible data files...

Here is a simple query, that could be used to find the current size of the tablespace and how much it can grow using the autoextensible feature of data file.


  SELECT tablespace_name,
         ROUND (SUM (bytes) / 1048576) curr_size,
         ROUND (SUM (GREATEST (bytes, DECODE (autoextensible, 'YES', maxbytes, 0)))/ 1048576)  growable
    FROM dba_data_files
GROUP BY tablespace_name
ORDER BY tablespace_name;