Mastering Oracle Datafiles: Queries, Creation, Resizing, and Management

1. Datafile Overview

In an Oracle database the SYSTEM and SYSAUX tablespaces must each have at least one datafile, and many other tablespaces have their own data and temporary files. Datafiles are OS files that store the logical structures of the database. Oracle assigns two kinds of identifiers: an absolute file number (unique across the database) and a relative file number (unique within a tablespace). Usually they match, but when the number of files exceeds a threshold (e.g., 1023) the relative numbers diverge, with large tablespaces starting at 1024.

Query absolute and relative file numbers:

<ol><li><code>select t.name tablespace_name, d.file#, d.rfile#, d.name file_name from v$tablespace t, v$datafile d where t.ts#=d.ts#;</code></li><li><code>TABLESPACE_NAME           FILE#   RFILE#   FILE_NAME</code></li><li><code>-------------------- ---------- ---------- --------------------------------------------------</code></li><li><code>SYSTEM                     1        1   /u01/app/oracle/oradata/stdb/system01.dbf</code></li><li><code>UNDOTBS1                  2        2   /u01/app/oracle/oradata/stdb/undotbs01.dbf</code></li><li><code>SYSAUX                   3        3   /u01/app/oracle/oradata/stdb/sysaux01.dbf</code></li><li><code>USERS                    4        4   /u01/app/oracle/oradata/stdb/users01.dbf</code></li><li><code>EXAMPLE                  5        5   /u01/app/oracle/oradata/stdb/example01.dbf</code></li><li><code>BIGTBS                  9      1024   /u01/app/oracle/oradata/stdb/bigfile01.dbf</code></li></ol>

The maximum number of datafiles is controlled by the CREATE DATABASE ... MAXDATAFILES clause and the DB_FILES initialization parameter, which also allocates SGA space for file metadata.

2. Creating and Adding Datafiles

When creating a tablespace you can specify a datafile directly, or add files later:

<ol><li><code>create tablespace my_tbs datafile '/path/my_tbs01.dbf' size 100M;</code></li><li><code>create temporary tablespace temp_tbs datafile '/path/temp01.dbf' size 50M;</code></li></ol>

To add a file to an existing tablespace:

<ol><li><code>alter tablespace my_tbs add datafile '/path/my_tbs02.dbf' size 200M;</code></li><li><code>alter tablespace my_tbs add tempfile '/path/temp02.dbf' size 100M;</code></li></ol>

3. Modifying Datafile Size

Enable automatic extension when creating a file:

<ol><li><code>create tablespace test_tbs datafile '/u01/app/oracle/oradata/stdb/test03.dbf' size 10M autoextend on next 1M maxsize 100M;</code></li></ol>

Turn auto‑extend on or off for an existing file:

<ol><li><code>alter database datafile '/u01/app/oracle/oradata/stdb/test02.dbf' autoextend on;</code></li><li><code>alter database datafile '/u01/app/oracle/oradata/stdb/test02.dbf' autoextend off;</code></li></ol>

Resize a file manually:

alter database datafile '/u01/app/oracle/oradata/stdb/test03.dbf' resize 20M;

4. Changing Datafile Availability

Datafiles can be taken offline or brought online. Offline files are not accessible until they are brought back online. In ARCHIVELOG mode you may need media recovery before a file can be brought online again.

alter database datafile '/u01/app/oracle/oradata/stdb/test03.dbf' offline;</code>
<code>alter database datafile '/u01/app/oracle/oradata/stdb/test03.dbf' online;

In non‑archivelog mode you can use offline for drop to mark a file as offline and ready for removal.

5. Renaming (Moving) Datafiles

Typical steps:

Take the tablespace offline.

Rename the OS file (e.g., mv test03.dbf test04.dbf).

Update the database metadata:

alter tablespace test_tbs rename datafile '/u01/app/oracle/oradata/stdb/test03.dbf' to '/u01/app/oracle/oradata/stdb/test04.dbf';

For files in different tablespaces you can rename multiple files in a single alter database rename file statement, then reopen the database.

6. Deleting Datafiles

Datafiles are removed with alter tablespace ... drop datafile or alter database datafile ... drop including datafiles. Several restrictions apply:

System tablespace files cannot be dropped.

Read‑only tablespace files that are locally managed cannot be dropped.

A tablespace that contains only one file cannot have that file removed.

The file must be empty and the tablespace must be online.

alter tablespace test_tbs drop datafile '/u01/app/oracle/oradata/stdb/test4.dbf';

Attempting to drop a protected file yields errors such as ORA‑01916 or ORA‑03264.