Mastering Oracle Datafiles: Creation, Modification, and Management Techniques

Overview of Oracle Datafiles and Tablespaces

In Oracle each tablespace must have at least one datafile. SYSTEM and SYSAUX tablespaces always contain a datafile; other tablespaces have their own datafiles and may have temporary files. Datafiles are operating‑system files that store the logical structures of the database. When a tablespace is created a datafile (or tempfile) must be specified.

File Numbers

Oracle assigns two identifiers to each file:

Absolute file number – unique across the whole database. Visible in v$datafile, v$tempfile, DBA_DATA_FILES or DBA_TEMP_FILES as the column FILE# or FILE_ID.

Relative file number – unique only within a tablespace. Usually the same as the absolute number, but diverges when the total number of datafiles exceeds the limit (e.g., 1023). Large‑file tablespaces start with relative number 1024.

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#;

Sample result (paths are illustrative):

TABLESPACE_NAME   FILE#   RFILE#   FILE_NAME
----------------  -----  ------  --------------------------------------------------
SYSTEM                1       1   /u01/app/oracle/oradata/stdb/system01.dbf
UNDOTBS1              2       2   /u01/app/oracle/oradata/stdb/undotbs01.dbf
SYSAUX                3       3   /u01/app/oracle/oradata/stdb/sysaux01.dbf
USERS                 4       4   /u01/app/oracle/oradata/stdb/users01.dbf
USERS                 8       8   /u01/app/oracle/oradata/stdb/user02.dbf
EXAMPLE               5       5   /u01/app/oracle/oradata/stdb/example01.dbf
TEST                  7       7   /u01/app/oracle/oradata/stdb/test02.dbf
TEST                  6       6   /u01/app/oracle/oradata/stdb/test01.dbf
BIGTBS                9    1024   /u01/app/oracle/oradata/stdb/bigfile01.dbf

Limits on the Number of Datafiles

The maximum number of datafiles that can be created is controlled by the CREATE DATABASE … MAXDATAFILES clause and the initialization parameter DB_FILES. Changing DB_FILES requires a database restart because the SGA allocates metadata structures based on its value. The operating system also imposes a limit on the number of open files; if the OS limit is reached Oracle may close files automatically, which can degrade performance. It is advisable to raise the OS limit above the expected number of online datafiles.

Creating and Adding Datafiles

Create a datafile while creating a tablespace

create tablespace my_tbs
  datafile '/path/to/file.dbf' size 100M;

create temporary tablespace temp_tbs
  tempfile '/path/to/temp.dbf' size 50M;

Add a datafile to an existing tablespace

alter tablespace my_tbs add datafile '/path/to/newfile.dbf' size 200M;
alter tablespace my_tbs add tempfile '/path/to/newtemp.dbf' size 100M;

Modifying Datafile Size

When a tablespace is created with the AUTOEXTEND clause the datafile can grow automatically.

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

Enable auto‑extend on an existing file:

alter database datafile '/u01/app/oracle/oradata/stdb/test02.dbf'
  autoextend on next 1M maxsize 100M;

Disable auto‑extend:

alter database datafile '/u01/app/oracle/oradata/stdb/test02.dbf' autoextend off;

Resize manually:

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

Changing Datafile Availability (Online / Offline)

A datafile can be taken offline and later brought online. In ARCHIVELOG mode the file may require media recovery before it can be opened.

-- Take a file offline
alter database datafile '/u01/app/oracle/oradata/stdb/test03.dbf' offline;

-- Bring it back online (may raise ORA‑01113 if recovery is needed)
alter database datafile '/u01/app/oracle/oradata/stdb/test03.dbf' online;

If recovery is required, run:

recover datafile 10;

In NOARCHIVELOG mode the clause offline for drop marks the file as offline and intended for removal, allowing the database to stay open.

alter database datafile 'D:\app\Manganese\oradata\orcl\test01.dbf' offline for drop;

Renaming (Moving) Datafiles

Take the tablespace that contains the file offline.

Rename the OS file with a system command (e.g., mv or rename).

Update the catalog with ALTER DATABASE … RENAME DATAFILE (or ALTER TABLESPACE … RENAME DATAFILE when the tablespace is offline).

Bring the tablespace back online.

Rename within the same tablespace:

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

Rename across tablespaces (must be performed while the instance is in MOUNTED state):

select status from v$instance;   -- should return MOUNTED
alter database rename file
  '/u01/app/oracle/oradata/stdb/test01.dbf',
  '/u01/app/oracle/oradata/stdb/test02.dbf',
  '/u01/app/oracle/oradata/stdb/test04.dbf'
to
  '/u01/app/oracle/oradata/stdb/test1.dbf',
  '/u01/app/oracle/oradata/stdb/test2.dbf',
  '/u01/app/oracle/oradata/stdb/test3.dbf';
alter database open;

Dropping Datafiles

Datafiles can be removed with ALTER TABLESPACE … DROP DATAFILE (for locally managed tablespaces) or ALTER DATABASE … DROP DATAFILE … INCLUDING DATAFILES (only for temporary files). The following restrictions apply:

Datafiles in dictionary‑managed read‑only tablespaces cannot be dropped.

System tablespace files are never droppable.

If a locally managed tablespace is offline, its files cannot be dropped.

A tablespace that contains a single datafile cannot drop that file.

Only empty datafiles (no allocated extents) may be dropped.

The file must be in an ONLINE state.

Examples of error messages when a restriction is violated:

alter tablespace test_tbs drop datafile '/u01/app/oracle/oradata/stdb/test4.dbf';
ORA-03264: cannot drop offline datafile of locally managed tablespace

alter tablespace test_tbs drop datafile '/u01/app/oracle/oradata/stdb/test3.dbf';
ORA-03261: the tablespace TEST_TBS has only one file

Note that ALTER DATABASE DATAFILE … DROP INCLUDING DATAFILES is valid only for temporary files; attempting it on a regular datafile produces ORA‑01916.

Key Precautions

Do not delete datafiles from dictionary‑managed read‑only tablespaces.

System tablespace files are immutable.

Bring a locally managed tablespace online before dropping its files.

A tablespace with a single datafile cannot lose that file.

Only empty datafiles may be removed.

Ensure the file is online (not in a restricted state) before issuing the drop command.