ORA-00059: Maximum Number of DB_FILES Exceeded - Increase Datafile Limit

ORA-00059: Maximum Number of DB_FILES Exceeded

Error Overview

Error Text: ORA-00059: maximum number of db_files exceeded

The ORA-00059 error occurs when an attempt is made to add a new datafile to the database but the number of currently registered datafiles has reached the maximum allowed. This limit is controlled by two separate settings that must both be considered: the DB_FILES initialization parameter and the MAXDATAFILES clause in the control file. Both limits must accommodate the desired datafile count, and the control file limit is the harder constraint — it requires recreating the control file to increase.

This error commonly surfaces during storage expansion activities, tablespace additions, or when a database that began life with modest storage requirements has grown substantially over time.

Common Causes

1. DB_FILES Parameter Too Low

The DB_FILES initialization parameter was set at database creation with a low value
Default value of DB_FILES is 200, which may be insufficient for large databases
Parameter was not sized for projected growth of the database

2. MAXDATAFILES in Control File Too Low

The control file was created with a MAXDATAFILES value smaller than current needs
MAXDATAFILES defaults vary by Oracle version and creation method
Unlike DB_FILES, this cannot be changed with ALTER SYSTEM — the control file must be recreated

3. Database Has Grown Beyond Initial Sizing

A long-lived database has accumulated datafiles over years of growth
Multiple rounds of tablespace additions have consumed the initial file limit
Partitioned tables, LOB segments, and temp files add to the total count

4. Datafiles Dropped but Slots Not Reclaimed

Previously dropped datafiles may still consume slots in the control file
File numbers in Oracle are not immediately reused after a datafile is dropped
Historical datafile records in the control file inflate the count

In RAC databases, all instances share the same control file limits
A limit appropriate for a single instance may be too small when multiple nodes add files

Diagnostic Queries

Check Current Datafile Count and Limits

-- Current number of datafiles
SELECT COUNT(*) AS current_datafile_count
FROM v$datafile;

-- Current number of tempfiles
SELECT COUNT(*) AS current_tempfile_count
FROM v$tempfile;

-- Combined total (datafiles + tempfiles share the DB_FILES limit in some versions)
SELECT
  (SELECT COUNT(*) FROM v$datafile) AS datafiles,
  (SELECT COUNT(*) FROM v$tempfile) AS tempfiles,
  (SELECT COUNT(*) FROM v$datafile) +
  (SELECT COUNT(*) FROM v$tempfile) AS total_files;

-- Current DB_FILES parameter value
SELECT name, value, description
FROM v$parameter
WHERE name = 'db_files';

Check MAXDATAFILES in the Control File

-- Check control file limits (including MAXDATAFILES)
SELECT
  cf_max_df   AS control_file_maxdatafiles,
  cf_max_logf AS control_file_maxlogfiles,
  cf_max_logm AS control_file_maxlogmembers,
  cf_max_inst AS control_file_maxinstances,
  cf_max_logh AS control_file_maxloghist
FROM v$controlfile_record_section
WHERE type = 'DATABASE';

-- Alternative: Use v$database for key limits
SELECT
  name,
  db_unique_name,
  controlfile_type,
  controlfile_created,
  controlfile_sequence#
FROM v$database;

-- Show control file record section for datafiles
SELECT
  type,
  record_size,
  records_total,
  records_used,
  records_total - records_used AS records_available
FROM v$controlfile_record_section
WHERE type IN ('DATAFILE', 'TEMPFILE', 'REDO LOG', 'ARCHIVED LOG')
ORDER BY type;

Inventory All Datafiles and Their Tablespaces

-- Full datafile inventory with tablespace and sizes
SELECT
  d.file#,
  d.name                                            AS file_path,
  t.name                                            AS tablespace_name,
  ROUND(d.bytes / 1024 / 1024 / 1024, 2)           AS size_gb,
  ROUND(d.maxbytes / 1024 / 1024 / 1024, 2)        AS max_size_gb,
  d.autoextensible,
  d.status
FROM v$datafile d
JOIN v$tablespace t ON d.ts# = t.ts#
ORDER BY d.file#;

-- Identify datafiles near their max size (candidates for replacement with larger files)
SELECT
  d.file#,
  d.name,
  ROUND(d.bytes / 1024 / 1024, 0)        AS current_mb,
  ROUND(d.maxbytes / 1024 / 1024, 0)     AS max_mb,
  ROUND(d.bytes / d.maxbytes * 100, 1)   AS pct_of_max
FROM v$datafile d
WHERE d.autoextensible = 'YES'
  AND d.maxbytes > 0
ORDER BY pct_of_max DESC;

-- Count files by tablespace
SELECT
  t.name         AS tablespace_name,
  COUNT(d.file#) AS datafile_count,
  ROUND(SUM(d.bytes) / 1024 / 1024 / 1024, 2)    AS total_size_gb,
  ROUND(SUM(d.maxbytes) / 1024 / 1024 / 1024, 2) AS max_size_gb
FROM v$datafile d
JOIN v$tablespace t ON d.ts# = t.ts#
GROUP BY t.name
ORDER BY datafile_count DESC;

Check How Close You Are to the Limit

-- Compare current usage against limits
SELECT
  (SELECT value FROM v$parameter WHERE name = 'db_files')   AS db_files_param,
  (SELECT COUNT(*) FROM v$datafile)                         AS current_datafiles,
  (SELECT value FROM v$parameter WHERE name = 'db_files') -
  (SELECT COUNT(*) FROM v$datafile)                         AS remaining_slots,
  ROUND(
    (SELECT COUNT(*) FROM v$datafile) /
    (SELECT value FROM v$parameter WHERE name = 'db_files') * 100,
    1
  )                                                         AS pct_used
FROM dual;

Step-by-Step Resolution

1. Increase DB_FILES Parameter (Dynamic — No Restart Required in Some Versions)

The DB_FILES parameter can be increased online in Oracle 12c and later:

-- Check current value
SHOW PARAMETER db_files;

-- Increase DB_FILES (effective immediately in 12c+, requires restart in earlier versions)
ALTER SYSTEM SET db_files = 500 SCOPE=SPFILE;

-- For immediate effect on 12c+
ALTER SYSTEM SET db_files = 500 SCOPE=BOTH;

-- Verify the change
SHOW PARAMETER db_files;

On Oracle 11g and earlier, a database restart is required for this change to take effect. Plan a maintenance window accordingly.

2. Increase MAXDATAFILES by Recreating the Control File

If the control file MAXDATAFILES limit is the bottleneck, you must recreate the control file. This requires a database restart:

-- Step 1: Generate a control file creation script from the current database
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

-- Find the trace file location
SELECT value FROM v$diag_info WHERE name = 'Diag Trace';

-- Step 2: The generated trace file contains a CREATE CONTROLFILE statement.
-- Edit it to increase MAXDATAFILES. Example script:

-- Shut down the database cleanly first
SHUTDOWN IMMEDIATE;

-- Step 3: Start in NOMOUNT mode
STARTUP NOMOUNT;

-- Step 4: Re-create the control file with a higher MAXDATAFILES value
CREATE CONTROLFILE REUSE DATABASE "ORCL"
  NORESETLOGS NOARCHIVELOG
  MAXLOGFILES 32
  MAXLOGMEMBERS 3
  MAXDATAFILES 1024        -- Increased from previous value
  MAXINSTANCES 8
  MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/oradata/orcl/redo01.log' SIZE 200M,
  GROUP 2 '/u01/oradata/orcl/redo02.log' SIZE 200M,
  GROUP 3 '/u01/oradata/orcl/redo03.log' SIZE 200M
DATAFILE
  '/u01/oradata/orcl/system01.dbf',
  '/u01/oradata/orcl/sysaux01.dbf',
  '/u01/oradata/orcl/undotbs01.dbf',
  '/u01/oradata/orcl/users01.dbf'
  -- ... add all datafiles listed in the trace output
CHARACTER SET AL32UTF8;

-- Step 5: Open the database
ALTER DATABASE OPEN;

-- Step 6: Verify the new limit
SELECT
  records_total AS maxdatafiles_in_controlfile,
  records_used  AS current_datafiles
FROM v$controlfile_record_section
WHERE type = 'DATAFILE';

3. Consolidate Datafiles (Alternative to Increasing Limits)

If increasing limits is not immediately possible, reduce datafile count by consolidating:

-- Option A: Resize existing datafiles to be larger and drop smaller ones
-- Step 1: Identify tablespaces with many small datafiles
SELECT
  tablespace_name,
  COUNT(*) AS file_count,
  ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2) AS total_gb
FROM dba_data_files
GROUP BY tablespace_name
HAVING COUNT(*) > 3
ORDER BY file_count DESC;

-- Step 2: Resize an existing datafile to absorb space from another
ALTER DATABASE DATAFILE '/u01/oradata/orcl/users01.dbf' RESIZE 50G;

-- Step 3: Move objects off the file to be removed
-- (Use Online Datafile Move in 12c+)
ALTER DATABASE MOVE DATAFILE '/u01/oradata/orcl/users_old.dbf'
TO '/u01/oradata/orcl/users_consolidated.dbf';

-- Step 4: Once the file is empty, drop it
ALTER TABLESPACE users DROP DATAFILE '/u01/oradata/orcl/users_old.dbf';

4. Enable Autoextend to Reduce Need for New Files

Reducing the frequency of new datafile additions by enabling autoextend on existing files:

-- Enable autoextend with a generous max size on existing datafiles
ALTER DATABASE DATAFILE '/u01/oradata/orcl/users01.dbf'
AUTOEXTEND ON NEXT 512M MAXSIZE 32G;

-- Apply autoextend to all datafiles in a tablespace that lack it
SELECT
  'ALTER DATABASE DATAFILE ''' || file_name || ''' AUTOEXTEND ON NEXT 256M MAXSIZE 32G;'
FROM dba_data_files
WHERE tablespace_name = 'USERS'
  AND autoextensible = 'NO';
-- Run the generated statements

5. Use Bigfile Tablespaces for New Growth

Bigfile tablespaces use a single very large datafile instead of many smaller ones, dramatically reducing datafile count:

-- Create a bigfile tablespace (single file up to 128TB)
CREATE BIGFILE TABLESPACE users_bf
DATAFILE '/u01/oradata/orcl/users_bf01.dbf'
SIZE 100G AUTOEXTEND ON MAXSIZE UNLIMITED;

-- Move segments to the bigfile tablespace
ALTER TABLE schema.large_table MOVE TABLESPACE users_bf;

-- Verify the tablespace type
SELECT tablespace_name, bigfile
FROM dba_tablespaces
WHERE tablespace_name = 'USERS_BF';

Prevention Strategies

1. Size DB_FILES and MAXDATAFILES Generously at Database Creation

-- During initial database creation (CREATE DATABASE statement)
-- Set generous values from the start:
-- DB_FILES = 1000 (in spfile/pfile)
-- MAXDATAFILES 1024 (in CREATE DATABASE or CREATE CONTROLFILE)

-- Verify initial settings at database creation time
-- Recommended minimum values for new databases:
-- DB_FILES = 500 to 1000
-- MAXDATAFILES = 1024

SELECT name, value
FROM v$parameter
WHERE name = 'db_files';

2. Monitor Datafile Count Trends

-- Create a monitoring query to alert when approaching limits
SELECT
  CASE
    WHEN (current_files / db_files_limit * 100) > 80
    THEN 'WARNING: Over 80% of DB_FILES limit used'
    WHEN (current_files / db_files_limit * 100) > 90
    THEN 'CRITICAL: Over 90% of DB_FILES limit used'
    ELSE 'OK'
  END AS status,
  current_files,
  db_files_limit,
  ROUND(current_files / db_files_limit * 100, 1) AS pct_used
FROM (
  SELECT
    (SELECT COUNT(*) FROM v$datafile)                            AS current_files,
    (SELECT TO_NUMBER(value) FROM v$parameter WHERE name = 'db_files') AS db_files_limit
  FROM dual
);

3. Prefer Bigfile Tablespaces for Large, Growing Data

Use bigfile tablespaces for large application schemas where growth is expected
Each bigfile tablespace uses exactly one datafile — no matter how large it grows
Reserve smallfile tablespaces for SYSTEM, SYSAUX, UNDO, and TEMP

4. Capacity Planning Guidelines

Plan DB_FILES and MAXDATAFILES for 5-year growth projections
Set MAXDATAFILES at least 20% higher than DB_FILES to handle the harder limit gracefully
Regularly audit tablespaces for over-fragmented file layouts
Set all datafiles to autoextend rather than adding new files whenever possible

Diagnostic Scripts

These Oracle Day by Day scripts can help monitor database storage and file counts:

health.sql — Overall database health including storage metrics
dtable.sql — Table and segment space analysis

ORA-01653 - Unable to extend table (tablespace full)
ORA-01654 - Unable to extend index
ORA-01658 - Unable to create INITIAL extent for segment
ORA-00959 - Tablespace does not exist

Emergency Response

Quick Fixes

Increase DB_FILES immediately (12c+)

ALTER SYSTEM SET db_files = 1000 SCOPE=BOTH;

Resize an existing datafile to buy time (avoids adding a new file)

ALTER DATABASE DATAFILE '/u01/oradata/orcl/users01.dbf' RESIZE 30G;

Enable autoextend on existing files to avoid the need to add new files

ALTER DATABASE DATAFILE '/u01/oradata/orcl/users01.dbf'
AUTOEXTEND ON NEXT 1G MAXSIZE 32G;

Post-Resolution Cleanup

-- Verify the new limit is in effect
SHOW PARAMETER db_files;

-- Check the control file MAXDATAFILES after recreation
SELECT records_total, records_used
FROM v$controlfile_record_section
WHERE type = 'DATAFILE';

-- Document the new baseline for capacity planning
SELECT
  COUNT(*)                                                  AS datafile_count,
  ROUND(SUM(bytes) / 1024 / 1024 / 1024, 2)               AS total_size_gb,
  ROUND(SUM(maxbytes) / 1024 / 1024 / 1024, 2)            AS max_possible_gb
FROM v$datafile;

-- Schedule a quarterly capacity review
-- Target: keep datafile count below 70% of DB_FILES limit