Skip to content

Autoextend property of a datafile – new oracle dba reference

Below article explains about the autoextend property of a datafile and howa new oracle dba should use it.

Changing Datafile Size 

This section describes the various ways to alter the size of a datafile, and contains the following topics:


Enabling and Disabling Automatic Extension for a Datafile

You can create datafiles or alter existing datafiles so that they automatically increase in size when more space is needed in the database. The file size increases in specified increments up to a specified maximum.

Setting your datafiles to extend automatically provides these advantages:

  • Reduces the need for immediate intervention when a tablespace runs out of space
  • Ensures applications will not halt or be suspended because of failures to allocate extents

To determine whether a datafile is auto-extensible, query the DBA_DATA_FILES view and examine the AUTOEXTENSIBLE column.

You can specify automatic file extension by specifying an AUTOEXTEND ON clause when you create datafiles using the following SQL statements:


You can enable or disable automatic file extension for existing datafiles, or manually resize a datafile, using the ALTER DATABASE statement. For a bigfile tablespace, you are able to perform these operations using the ALTER TABLESPACE statement.

The following example enables automatic extension for a datafile added to the users tablespace:


    ADD DATAFILE ‘/u02/oracle/rbdb1/users03.dbf’ SIZE 10M


      NEXT 512K

      MAXSIZE 250M;

The value of NEXT is the minimum size of the increments added to the file when it extends. The value of MAXSIZE is the maximum size to which the file can automatically extend.

The next example disables the automatic extension for the datafile.

ALTER DATABASE DATAFILE ‘/u02/oracle/rbdb1/users03.dbf’


AUTOEXTEND. Enables or disables the automatic extension of datafile.

OFF. Disables AUTOEXTEND if it is turned on. NEXT and MAXSIZE are set to zero. Values for NEXT and MAXSIZE must be respecified in later ALTER TABLESPACE AUTOEXTEND commands if OFF is specified; they are not persistent values.


NEXT. Disk space to allocate to the datafile when more extents are required.

MAXSIZE. Maximum disk space allowed for allocation to the datafile.

UNLIMITED. Set no limit on allocating disk space to the datafile.

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Post a Comment

You must be logged in to post a comment.