Scops
Docs Download

Oracle problems

ORA-00059: Maximum Number of DB_FILES Exceeded, and the Fix

3 November 2026

ORA-00059 arrives at the worst moment: a tablespace is full, the DBA adds a datafile, and Oracle refuses, because the database already has as many datafiles as DB_FILES allows. The parameter cannot be changed while the instance runs, so the only real fix is a restart, and the tablespace stays full until then. Nothing about it is sudden. The count climbs by one file at a time, for months, in plain sight in V$DATAFILE, and the query below shows how close it is.

What the limit is

Oracle's reference says DB_FILES specifies the maximum number of database files that can be opened for this database. The default is 200. It is not modifiable: a new value goes into the spfile and takes effect at the next startup, and in Oracle RAC every instance must have the same value. The minimum value is the largest absolute file number in the database, which is why the highest FILE# matters as much as the count. The control file has a limit of its own, MAXDATAFILES from CREATE CONTROLFILE, but that one takes care of itself: when a datafile is added above MAXDATAFILES but within DB_FILES, Oracle expands the control file automatically.

How close you are

SELECT (SELECT COUNT(*)   FROM v$datafile)  AS datafiles,
       (SELECT MAX(file#) FROM v$datafile)  AS highest_file#,
       (SELECT TO_NUMBER(value) FROM v$parameter
        WHERE  name = 'db_files')           AS db_files
FROM   dual;

Compare the higher of the two figures with DB_FILES. The control file side, for completeness:

SELECT type, records_total, records_used
FROM   v$controlfile_record_section
WHERE  type = 'DATAFILE';

RECORDS_TOTAL is the MAXDATAFILES the control file was created with, and RECORDS_USED how many of the slots are taken. It grows on its own, so it is a figure to know, not a limit to manage.

How fast it climbs

SELECT TO_CHAR(creation_time, 'YYYY-MM') AS month, COUNT(*) AS added
FROM   v$datafile
WHERE  creation_time > ADD_MONTHS(SYSDATE, -12)
GROUP  BY TO_CHAR(creation_time, 'YYYY-MM')
ORDER  BY 1;

Twelve months of additions give the rate, and the rate gives the date the limit is reached. Three files a month against 40 left is a year; a migration that adds 30 tablespaces is next week.

Where the files come from

SELECT tablespace_name, COUNT(*) AS files,
       ROUND(SUM(bytes)/1024/1024/1024)    AS gb,
       ROUND(SUM(maxbytes)/1024/1024/1024) AS max_gb
FROM   dba_data_files
GROUP  BY tablespace_name
ORDER  BY files DESC;

The tablespace at the top of the list is almost always a smallfile tablespace that grew the only way a smallfile tablespace can. A datafile holds at most 4 million blocks, 32 GB at the usual 8 KB block size, so a 2 TB tablespace is at least 64 files, and every time it fills someone adds one more. The count that hits DB_FILES is that habit, multiplied by the years.

The fix, in order

  1. Raise DB_FILES, with room, and plan the restart. ALTER SYSTEM SET db_files = 2000 SCOPE = SPFILE is the whole change; the ceiling is the operating system's, and the next change is another restart, so set a value for years rather than months. In RAC, set it for all instances and restart them all.
  2. Until the restart, grow instead of adding. The error stops new files, not bigger ones. ALTER DATABASE DATAFILE '...' RESIZE or AUTOEXTEND ON MAXSIZE on a file that has room under the 32 GB ceiling keeps the tablespace writable without a new file number.
  3. Put new growth in bigfile tablespaces. A bigfile tablespace has one datafile, up to 4 billion blocks, and never adds a second, so its file count is one for good. A smallfile tablespace cannot be converted in place: the segments move to a new bigfile tablespace with ALTER TABLE ... MOVE and ALTER INDEX ... REBUILD, online on Enterprise Edition, or with Data Pump.
  4. Stop the habit. Autoextend to the file's maximum first, a second file only when the first is at the ceiling, and a bigfile tablespace for anything that will pass a terabyte. The file count then follows the data, not the number of full-tablespace alerts.
  5. Check MAXDATAFILES only if you rebuild the control file. It expands automatically in normal operation; a CREATE CONTROLFILE written by hand with a small value is the one way to bring it back as a limit.

See it coming

Radar Engine, the detection layer of Scops, counts the datafiles of every database each hour against its DB_FILES, forecasts the date the limit is reached from the rate at which files are added, and forecasts the space behind them, so the warning arrives when the fix is still a planned restart rather than a full tablespace that cannot be extended:

DetectorClockWhen it fires
Datafiles near the limitHourly, against db_files.85%: Watch. Also full within 30 days: Warning. Under 3 left at 98%, or full within 2 days: Critical.
Datafile space running outDaily forecast: datafiles to their maximum size, then ASM.The date when ASM is full, against horizons of 14, 45 and 90 days. Else Watch.

The finding names the database, the files in use against the limit, the rate and the date. Scops needs no agent on the server and no Diagnostics Pack for any of it, and the check runs on Standard Edition 2 as on Enterprise Edition.

Questions

What does ORA-00059 mean?
The database already has as many datafiles as the DB_FILES parameter allows, so a new datafile cannot be added. DB_FILES defaults to 200, and the fix is a higher value in the spfile and a restart of the instance.

Can I change DB_FILES without a restart?
No. The parameter is not modifiable while the instance runs; ALTER SYSTEM SET db_files SCOPE = SPFILE prepares the change and the next startup applies it. In Oracle RAC all instances must use the same value.

Do I also need to change MAXDATAFILES in the control file?
Usually not. When a datafile is added above MAXDATAFILES but within DB_FILES, Oracle expands the control file automatically. V$CONTROLFILE_RECORD_SECTION shows the slots in use for the DATAFILE type.

Why does my database have hundreds of datafiles?
Because a smallfile datafile holds at most 4 million blocks, 32 GB with an 8 KB block, and a large smallfile tablespace gets a new file every time it fills. Bigfile tablespaces hold one file of up to 4 billion blocks, and keep the count flat.

Related problems

Sources