Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
this thread: dba.stackexchange.com/questions/15790/reduce-size-of-sysaux might be of some use –  ipip Jan 30 '13 at 12:28
add comment

migrated from stackoverflow.com Jan 28 '13 at 10:02

This question came from our site for professional and enthusiast programmers.

3 Answers

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...)

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.