How to Add a Datafile to Oracle Tablespace

How to Add a Datafile to an Oracle Tablespace

When a tablespace fills up or approaches its MAXSIZE limit, you add a new datafile. This is the standard, zero-downtime way to expand tablespace capacity — no restart required, and applications continue running throughout.

Prerequisites

You need the ALTER TABLESPACE system privilege (or SYSDBA). For tempfiles, the same applies. Before adding a datafile, confirm:

-- Check current tablespace usage and identify which one is full
SELECT
  t.tablespace_name,
  ROUND(SUM(d.bytes)/1024/1024/1024, 2)   AS total_gb,
  ROUND(SUM(NVL(f.free,0))/1024/1024/1024, 2) AS free_gb,
  ROUND((1 - SUM(NVL(f.free,0)) / SUM(d.bytes)) * 100, 1) AS used_pct
FROM dba_tablespaces t
JOIN dba_data_files d ON t.tablespace_name = d.tablespace_name
LEFT JOIN (
  SELECT tablespace_name, SUM(bytes) AS free
  FROM dba_free_space
  GROUP BY tablespace_name
) f ON t.tablespace_name = f.tablespace_name
GROUP BY t.tablespace_name
HAVING ROUND((1 - SUM(NVL(f.free,0)) / SUM(d.bytes)) * 100, 1) > 80
ORDER BY used_pct DESC;

-- Check existing datafiles for the target tablespace
SELECT file_id, file_name,
       ROUND(bytes/1024/1024, 0)    AS size_mb,
       autoextensible,
       ROUND(maxbytes/1024/1024/1024, 2) AS max_gb,
       status
FROM dba_data_files
WHERE tablespace_name = 'APP_DATA'
ORDER BY file_id;

Step 1: Add a Datafile to a Permanent Tablespace

-- Add a single datafile on a filesystem
ALTER TABLESPACE app_data
  ADD DATAFILE '/u01/oradata/ORCL/app_data02.dbf' SIZE 500M
  AUTOEXTEND ON NEXT 100M MAXSIZE 10G;

-- Add a datafile on ASM (production standard)
ALTER TABLESPACE app_data
  ADD DATAFILE '+DATA' SIZE 1G
  AUTOEXTEND ON NEXT 256M MAXSIZE 20G;

Oracle automatically names the ASM file using OMF (Oracle Managed Files) naming conventions when you specify only the disk group.

Step 2: Add a Tempfile to a Temporary Tablespace

Temporary tablespaces use tempfiles, not datafiles. The syntax is slightly different.

-- Add a tempfile to the default temp tablespace
ALTER TABLESPACE temp
  ADD TEMPFILE '/u01/oradata/ORCL/temp02.dbf' SIZE 2G
  AUTOEXTEND ON NEXT 512M MAXSIZE 10G;

-- Verify tempfiles
SELECT file_name, bytes/1024/1024 AS size_mb, autoextensible,
       maxbytes/1024/1024/1024 AS max_gb, status
FROM dba_temp_files
WHERE tablespace_name = 'TEMP';

Step 3: Add Multiple Datafiles at Once

-- Add two datafiles in one statement (spread across different mount points)
ALTER TABLESPACE reporting
  ADD DATAFILE '/u02/oradata/ORCL/rpt02.dbf' SIZE 5G AUTOEXTEND OFF,
               '/u03/oradata/ORCL/rpt03.dbf' SIZE 5G AUTOEXTEND OFF;

Step 4: Configure Autoextend on an Existing Datafile

Sometimes the simplest fix is enabling autoextend on a datafile that already exists but has it turned off.

-- Enable autoextend on an existing datafile
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/app_data01.dbf'
  AUTOEXTEND ON NEXT 100M MAXSIZE 20G;

-- Disable autoextend (to cap growth at current size)
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/app_data01.dbf'
  AUTOEXTEND OFF;

-- Change the MAXSIZE limit on an existing datafile
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/app_data01.dbf'
  AUTOEXTEND ON NEXT 256M MAXSIZE 50G;

Step 5: Using Oracle Managed Files (OMF)

With OMF, Oracle names and places datafiles automatically. This eliminates manual path management.

-- Check if OMF is configured
SHOW PARAMETER db_create_file_dest;

-- Configure OMF (if not already set)
ALTER SYSTEM SET db_create_file_dest = '+DATA' SCOPE=BOTH;

-- Add a datafile using OMF — no path needed
ALTER TABLESPACE app_data ADD DATAFILE SIZE 1G
  AUTOEXTEND ON NEXT 256M MAXSIZE 20G;

-- Oracle will create something like:
-- +DATA/ORCL/DATAFILE/app_data.276.1158000123

Advanced Examples

Add Datafile to a Bigfile Tablespace

Bigfile tablespaces have exactly one datafile — you cannot add more. Instead, resize the existing one.

-- For bigfile tablespaces: resize, do NOT add datafile
ALTER TABLESPACE dw_data RESIZE 100G;

-- Or resize via datafile reference
ALTER DATABASE DATAFILE '+DATA/ORCL/DATAFILE/dw_data.dbf' RESIZE 100G;

Automate Datafile Addition When Near Full

-- PL/SQL block to add a datafile when tablespace exceeds 85% full
DECLARE
  v_pct_full NUMBER;
  v_ts_name  VARCHAR2(30) := 'APP_DATA';
BEGIN
  SELECT ROUND((1 - SUM(NVL(f.bytes,0)) / SUM(d.bytes)) * 100, 1)
  INTO v_pct_full
  FROM dba_data_files d
  LEFT JOIN dba_free_space f
    ON d.tablespace_name = f.tablespace_name
   AND d.file_id = f.file_id
  WHERE d.tablespace_name = v_ts_name;

  IF v_pct_full > 85 THEN
    EXECUTE IMMEDIATE
      'ALTER TABLESPACE ' || v_ts_name ||
      ' ADD DATAFILE SIZE 1G AUTOEXTEND ON NEXT 256M MAXSIZE 20G';
    DBMS_OUTPUT.PUT_LINE('Added datafile to ' || v_ts_name);
  ELSE
    DBMS_OUTPUT.PUT_LINE('Tablespace ' || v_ts_name || ' is ' || v_pct_full || '% full — OK');
  END IF;
END;
/

Common Mistakes and Pitfalls

Adding a datafile to the wrong location — Always confirm the mount point has sufficient space before adding. Check with df -h (OS level) or query v$asm_diskgroup for ASM.

Forgetting MAXSIZE — AUTOEXTEND ON without MAXSIZE allows the file to grow until the disk is full. Always set a practical ceiling.

Adding a datafile to a bigfile tablespace — Oracle will raise ORA-32771. Use ALTER TABLESPACE ... RESIZE instead.

Mixing disk groups for striped tablespaces — If you have two datafiles on the same disk group, you lose the I/O benefit of striping. Spread across different disk groups or mount points.

Adding tempfiles after database recreation — If you recreate a database or restore from backup, temp tablespace tempfiles are not backed up by RMAN. You must add them manually.

Verification Queries

-- Confirm the new datafile is online and sized correctly
SELECT
  d.file_id,
  d.file_name,
  ROUND(d.bytes/1024/1024/1024, 2) AS size_gb,
  d.autoextensible,
  ROUND(d.increment_by * 8192/1024/1024, 0) AS next_mb,
  ROUND(d.maxbytes/1024/1024/1024, 2) AS max_gb,
  d.status
FROM dba_data_files d
WHERE d.tablespace_name = 'APP_DATA'
ORDER BY d.file_id;

-- Confirm the new free space is available in the tablespace
SELECT
  tablespace_name,
  COUNT(*)                             AS free_extents,
  ROUND(SUM(bytes)/1024/1024, 0)      AS total_free_mb,
  ROUND(MAX(bytes)/1024/1024, 0)      AS largest_free_mb
FROM dba_free_space
WHERE tablespace_name = 'APP_DATA'
GROUP BY tablespace_name;

-- Show total allocated vs. used after adding datafile
SELECT
  d.tablespace_name,
  ROUND(SUM(d.bytes)/1024/1024/1024, 2) AS total_gb,
  ROUND(SUM(NVL(f.bytes,0))/1024/1024/1024, 2) AS free_gb,
  ROUND((1 - SUM(NVL(f.bytes,0))/SUM(d.bytes)) * 100, 1) AS used_pct
FROM dba_data_files d
LEFT JOIN dba_free_space f
  ON d.tablespace_name = f.tablespace_name
 AND d.file_id = f.file_id
WHERE d.tablespace_name = 'APP_DATA'
GROUP BY d.tablespace_name;

Related Topics

• How to Create a Tablespace - Create tablespaces from scratch
• How to Resize Datafiles - Grow or shrink existing datafiles
• How to Monitor Tablespace Usage - Proactive space monitoring
• Administration Scripts - Comprehensive DBA toolkit