How to Take a Datafile out of AUTOEXTEND Mode

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.

SQL> select d.file#, d.name, f.maxextend, f.inc
from v$dbfile d, SYS.filext$ f
where d.file# = f.file#;

FILE#     NAME                                                 MAXEXTEND     INC
---------- ------------------------------ ---------- ----------
3         /u05/oradata/<SID>/temp02.dbf     4194302              2560
5         /u05/oradata/<SID>/users01.dbf     4194302             2560

The output returned are the datafiles with autoextend capabilities.


In Oracle, a datafile is a physical file on disk that stores the data for a tablespace. The AUTOEXTEND feature allows a datafile to automatically increase in size when it runs out of space. However, there might be situations where you want to disable this feature to prevent uncontrolled growth of the datafile. You can turn off the AUTOEXTEND feature for a datafile using the ALTER DATABASE statement.

Here’s how you can turn off the AUTOEXTEND feature for a datafile:

Step-by-Step Example

1. Identify the Datafile

First, identify the tablespace and datafile you want to modify. You can query the DBA_DATA_FILES view to get this information.

SELECT file_name, tablespace_name, autoextensible
FROM dba_data_files
WHERE tablespace_name = 'YOUR_TABLESPACE_NAME';

This query will return a list of datafiles for the specified tablespace, along with their AUTOEXTENSIBLE status.

2. Turn Off AUTOEXTEND

Use the ALTER DATABASE statement to turn off AUTOEXTEND for the specific datafile. Here’s the syntax:

ALTER DATABASE DATAFILE 'datafile_name' AUTOEXTEND OFF;

Replace 'datafile_name' with the actual path and name of your datafile.

Example

Assume you have a datafile named '/u01/app/oracle/oradata/mydb/users01.dbf' for the tablespace USERS. Here’s how you can turn off AUTOEXTEND:

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/mydb/users01.dbf' AUTOEXTEND OFF;

Verifying the Change

After turning off AUTOEXTEND, you can verify the change by querying the DBA_DATA_FILES view again:

SELECT file_name, tablespace_name, autoextensible
FROM dba_data_files
WHERE file_name = '/u01/app/oracle/oradata/mydb/users01.dbf';

The AUTOEXTENSIBLE column should now show NO, indicating that AUTOEXTEND is turned off for the datafile.

Considerations

1. Monitoring Free Space: With AUTOEXTEND turned off, it’s crucial to monitor the free space in the tablespace to avoid running out of space, which could lead to application errors.

2. Manual Extension: If more space is needed, you can manually resize the datafile using the ALTER DATABASE statement:

   ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/mydb/users01.dbf' RESIZE new_size;
   

   Replace new_size with the desired size (e.g., 500M, 2G).

3. Performance: Disabling AUTOEXTEND can help prevent unexpected growth of datafiles, which might be important for performance tuning and capacity planning.

4. Disk Space Management: Ensure there is adequate disk space available if you plan to manually manage the size of datafiles, especially in environments with high data growth.

By following these steps, you can effectively turn off the AUTOEXTEND feature for a datafile in Oracle, giving you greater control over the size and growth of your database files.