SYSAUX tablespace is 98%

Question

My SYSAUX tablespace is 98% full. In my tablespace statement AUTOEXTENSIBLE=yes.

So I want to know that will it add a new data file if it reaches 100% or if not then should I make makesize to unlimited?

Answer 1

Make sure

• you have enough disk space available - check all mount points
• your files are allowed to autoextend in dba_data_files.
• next allocation size is not too big (important if you are getting closer to free space limit...)

Answer 2

Oracle will not create a new datafile when the current one becomes full.

I suggest:

• Set the current datafile to AUTOEXTEND, but only up to a size smaller than the free space on the disk it resides on
• Create another datafile for the tablespace that can be used once the 1st datafile becomes full
• Pro-actively monitor the free space in the tablespace and the disk(s) the data files reside on

There may also be tables in the SYSAUX tablespace that can be purged. The most likely culprit is usually old AWR snapshots. You can query the DBA_EXTENTS data dictionary view (group by SEGMENT_NAME, filter on TABLESPACE_NAME='SYSAUX') to get a better idea of why the tablespace is growing.

Answer 3

No, Oracle won't add an extra datafile.

Normally the SYSAUX tablespace is more or less stable so it would be smart to check what is eating the space in there. Connected as a DBA user, run the script ${ORACLE_HOME}/rdbms/admin/utlsyxsz to get the current usage of the SYSAUX tablespace and see how it will grow when you change certain parameters for which you are asked to enter values.

See Oracle® Database Administrator's Guide 11g to get an idea of regular sizing.