Question: How do you take a datafile out of autoextend mode?
Oracle datafiles were given the ability to extend automatically if needed from a long time. If the given datafile is unable to allocate the space needed, it can increase the size of the datafile to make space for objects to grow.
To determine if the datafile has autoextend capabilities, query the dba_data_files table. For example:
SQL> select file_name, autoextensible from dba_data_files; FILE_NAME AUT ---------------------------------------- --- /u05/app/oracle/oradata/[SID]/tools01.dbf YES /u05/app/oracle/oradata/[SID]/drsys01.dbf YES /u05/app/oracle/oradata/[SID]/users01.dbf YES /u05/app/oracle/oradata/[SID]/indx01.dbf YES /u05/app/oracle/oradata/[SID]/rbs01.dbf NO /u05/app/oracle/oradata/[SID]/temp01.dbf YES /u05/app/oracle/oradata/[SID]/system01.dbf NO
The rows that have YES for AUTOEXTENSIBLE are the datafiles with the autoextend capabilities.
To DISABLE the autoextend capabilities of any of the datafiles in oracle database, issue:
SQL> alter database datafile '[datafile name]' AUTOEXTEND OFF;