How to do autoextend on or off of a DATAFILE. | Oracle

Sometimes we need to change the autoextend of datafile ON or OFF as per our work demands. Here only USERS tablespace is being tested.


CHECK THE STATUS IF AUTOEXTEND IS ON or OFF
SQL>  select FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE,sum(bytes/1024/1024/1024) from dba_data_files where TABLESPACE_NAME='USERS' group by FILE_NAME,TABLESPACE_NAME, AUTOEXTENSIBLE;


--------------------------------------------------------------------
FILE_NAME             AUT  SUM(BYTES/1024/1024/1024) TABLESPACE_NAME
--------------------- --- -------------------------  ---------------
/oradata/users01.dbf  NO    31.9999847                 USERS
                                           
/oradata/users02.dbf  NO    31.9992676                 USERS
                                         

TO TURN ON :
SQL> alter database datafile '/oradata/users01.dbf' autoextend on;

Database altered.

SQL> alter database datafile '/oradata/users02.dbf' autoextend on;

Database altered.

Then check the status of the same.
SQL>  select FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE,sum(bytes/1024/1024/1024) from dba_data_files where TABLESPACE_NAME='USERS' group by FILE_NAME,TABLESPACE_NAME, AUTOEXTENSIBLE;


--------------------------------------------------------------------
FILE_NAME             AUT  SUM(BYTES/1024/1024/1024) TABLESPACE_NAME
--------------------- --- -------------------------  ---------------
/oradata/users01.dbf  YES   31.9999847                 USERS
                                           
/oradata/users02.dbf  YES   31.9992676                 USERS


TO TURN OFF :
SQL> alter database datafile '/oradata/users01.dbf' autoextend off;

Database altered.

SQL> alter database datafile '/oradata/users02.dbf' autoextend off;

Database altered.