Enable auto extend the datafile size in oracle database.

Enable auto extend |Before enabling the auto extend datafile size in oracle database. Make sure free space available on disk. Some time we need to enable auto increment for datafile to extend its size while data growing up frequently.



Enable auto extend


How to Enable auto extend.


First of all, we have to check whether auto extend enable or not on particular datafile.

SQL>select file_name,tablespace_name,bytes/1024,AUTOEXTENSIBLE,status,increment_by from dba_data_files where tablespace_name=’APPS_TS_TX_IDX’ order by tablespace_name;

Using below command, we can enable autoextend datafile by 200m upto 30g.

SQL>alter database datafile ‘/data03/db/apps_st/data/a_txn_ind23.dbf’ autoextend on  next 200m maxsize 30g;

Database altered.


Leave a Comment