Enabling and Disabling Automatic Extension for a Datafile[akadia]

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

Advantage of using AUTOEXTEND ON

• Reduces the need for immediate intervention when a tablespace runs out of space.

• Ensures applications will not halt because of failures to allocate extents.

Disadvantage of using AUTOEXTEND ON

If you have no automatic mechanism to check the growth of your Oracle Datafile, ATOEXTEND ON will fillup the filesystem until 100%, then it is not easily to reclaim space. We think, that the disadvantage is greater than the advantage, due to this, check your Oracle Datafiles and reset AUTOEXTEND to OFF.

Check AUTOEXTEND on the Oracle Datafiles

To find out if a datafile is auto-extensible, query the DBA_DATA_FILES view and examine the AUTOEXTENSIBLE column.

SELECT file_name,autoextensible FROM dba_data_files;

FILE_NAME AUT
----------------------------------- ---
/u01/db/DIA3/sys/DIA3_sys1.dbf NO
/u01/db/DIA3/cdr/DIA3_cdr1.dbf NO
/u01/db/DIA3/cre/DIA3_cre1.dbf NO
/u01/db/DIA3/rbs/DIA3_rbs1.dbf NO
/u01/db/DIA3/usr/DIA3_users1.dbf NO
/u01/db/DIA3/tab/DIA3_tab1.dbf YES

Enabling automatic file extension

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

• CREATE DATABASE
• CREATE TABLESPACE
• ALTER TABLESPACE

CREATE TABLESPACE rbs
DATAFILE '/u01/db/DIA3/rbs/DIA3_rbs1.dbf' SIZE 512064K REUSE
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
PERMANENT
ONLINE;

You can enable or disable automatic file extension for existing datafiles, or manually resize a datafile using the SQL statement ALTER DATABASE. The following example enables automatic extension for a datafile added to the USERS tablespace:

ALTER TABLESPACE users
ADD DATAFILE '/u02/oracle/rbdb1/users03.dbf' SIZE 10M
AUTOEXTEND ON
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.

Disabling automatic file extension

The next example disables the automatic extension for the datafile.

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf'
AUTOEXTEND OFF;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/936/viewspace-60600/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/936/viewspace-60600/