How to enable Autoextend on in Oracle

>> Adding data file with Auto-extend on option.

SQL> ALTER TABLESPACE USERS ADD DATAFILE 'J:\ORADATA\KPILIVE\USERS03.DBF' SIZE 10G AUTOEXTEND ON NEXT 1024M;

Tablespace altered.


>> Autoextend the existing datafile to 1024m.

SQL> ALTER DATABASE DATAFILE 'J:\ORADATA\KPILIVE\USERS01.DBF' AUTOEXTEND ON NEXT 1024M;

Database altered.


>> Now query the auto extensible is enabled or not.


SQL> col FileName format a15
SQL> col AUTO format a5
SQL> select tablespace_name,file_name,round(bytes/1024/1024,0) bytesMB, autoextensible AUTO,(increment_by*8192)/1024/1024  Incrmnt,round(maxbytes/1024/1024,2) MaxBytes from dba_data_files;

TABLESPACE                 FILE_NAME                BYTESMB        AUTO  INCRMNT     MAXBYTES
------------------------------------------------------------------------------------------------------- -------------        
USERS            G:\IECT EST2\USERS01.DBF         18560           YES        1024          32767.98
         
SYSAUX         G:\IECTEST2\SYSAUX01.DBF        5120              YES         1024          32767.98
     
SYSTEM         G:\IECTEST2\SYSTEM01.DBF         5120              YES         1024          32767.98
       
UNDONEW     G:\IECTEST2\UNDOTBS009           30720           YES          1024           32767.98
         
4 rows selected.


Happy Reading ....