DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01118 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01118: cannot add any more database files: limit exceeded

ORA-01118 is thrown when Oracle cannot add any more datafiles to the database because the configured limit has been reached. This limit is controlled by either the DB_FILES initialization parameter or the MAXDATAFILES setting stored in the control file, whichever is lower. You'll typically encounter this error when executing ALTER TABLESPACE ... ADD DATAFILE or CREATE TABLESPACE statements.

Top 3 Causes

1. DB_FILES Parameter Limit Reached

The DB_FILES parameter defines the maximum number of datafiles the instance can have open simultaneously. When the number of datafiles in V$DATAFILE equals this value, Oracle refuses to add more.

-- Check current datafile count vs. DB_FILES limit
SELECT COUNT(*) AS current_files,
       (SELECT TO_NUMBER(VALUE)
        FROM V$PARAMETER
        WHERE NAME = 'db_files') AS db_files_limit
FROM V$DATAFILE;
Enter fullscreen mode Exit fullscreen mode

2. MAXDATAFILES in Control File Exceeded

The control file has its own hard limit set by MAXDATAFILES at database creation time. Even if DB_FILES is large enough, this control file-level limit can block new datafile additions independently.

-- Check MAXDATAFILES limit recorded in the control file
SELECT CF_MAX_FILES AS maxdatafiles_controlfile
FROM V$DATABASE;

-- Review all current datafiles and their status
SELECT FILE#, STATUS, NAME, BYTES/1024/1024 AS SIZE_MB
FROM V$DATAFILE
ORDER BY FILE#;
Enter fullscreen mode Exit fullscreen mode

3. Accumulated Unused Datafiles

Over time, test tablespaces, migration leftovers, and offline datafiles consume slots without providing business value. These stale entries still count against the limit even if the tablespace is no longer active.

-- Identify offline or unused datafiles
SELECT d.FILE#, d.STATUS, d.NAME, t.TABLESPACE_NAME, t.STATUS AS TS_STATUS
FROM V$DATAFILE d
JOIN DBA_DATA_FILES f ON d.NAME = f.FILE_NAME
JOIN DBA_TABLESPACES t ON f.TABLESPACE_NAME = t.TABLESPACE_NAME
WHERE d.STATUS = 'OFFLINE'
   OR t.STATUS = 'OFFLINE'
ORDER BY d.FILE#;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Increase DB_FILES (Requires Restart) 

-- Increase DB_FILES to 500 using SPFILE
ALTER SYSTEM SET DB_FILES = 500 SCOPE = SPFILE;

-- Restart the database to apply
SHUTDOWN IMMEDIATE;
STARTUP;

-- Verify
SHOW PARAMETER DB_FILES;
Enter fullscreen mode Exit fullscreen mode

Recreate the Control File (MAXDATAFILES Fix) 

-- Generate a controlfile recreation script
ALTER DATABASE BACKUP CONTROLFILE TO TRACE
  AS '/tmp/cf_recreate.sql' REUSE NORESETLOGS;

-- Edit the script: set MAXDATAFILES to a larger value, then run:
STARTUP NOMOUNT;

CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024    -- increase this value
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
    GROUP 1 '/u01/oradata/ORCL/redo01.log' SIZE 200M BLOCKSIZE 512,
    GROUP 2 '/u01/oradata/ORCL/redo02.log' SIZE 200M BLOCKSIZE 512
DATAFILE
    '/u01/oradata/ORCL/system01.dbf',
    '/u01/oradata/ORCL/sysaux01.dbf',
    '/u01/oradata/ORCL/undotbs01.dbf',
    '/u01/oradata/ORCL/users01.dbf'
CHARACTER SET AL32UTF8;
Enter fullscreen mode Exit fullscreen mode

⚠️ Warning: Always take a full backup before recreating the control file.

Drop Unused Tablespaces 

-- Drop an unused tablespace and its physical files
DROP TABLESPACE old_test_ts INCLUDING CONTENTS AND DATAFILES;
Enter fullscreen mode Exit fullscreen mode

Enable AUTOEXTEND to Reduce Need for New Files 

-- Enable autoextend on an existing datafile
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/users01.dbf'
AUTOEXTEND ON NEXT 512M MAXSIZE 32767M;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Monitor usage proactively. Set up an alert when datafile count exceeds 80% of DB_FILES.

-- Usage monitoring query
SELECT CURRENT_COUNT,
       DB_FILES_LIMIT,
       ROUND(CURRENT_COUNT / DB_FILES_LIMIT * 100, 2) AS PCT_USED
FROM (
    SELECT COUNT(*) AS CURRENT_COUNT,
           TO_NUMBER((SELECT VALUE FROM V$PARAMETER WHERE NAME='db_files')) AS DB_FILES_LIMIT
    FROM V$DATAFILE
);
Enter fullscreen mode Exit fullscreen mode

2. Use BigFile Tablespaces where possible. A BigFile tablespace uses exactly one datafile (up to 128TB), drastically reducing the total datafile count and the risk of hitting this limit.

-- Create a BigFile tablespace (one file = one tablespace)
CREATE BIGFILE TABLESPACE app_data_ts
DATAFILE '/u01/oradata/ORCL/app_data01.dbf'
SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;
Enter fullscreen mode Exit fullscreen mode

Related Errors

  • ORA-00059 – Maximum number of DB_FILES exceeded; closely related to ORA-01118.
  • ORA-01119 – Error creating a database file; often appears alongside ORA-01118.
  • ORA-01565 – Error identifying a file; can accompany datafile addition failures.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)