How to Fix ORA-00059: Maximum Number of DB_FILES Exceeded in Oracle

If you’re working with Oracle databases and hit this error:

ORA-00059: maximum number of DB_FILES exceeded

Don’t worry — this just means your database has reached its configured limit for how many datafiles it can manage. This guide will show you why it happens, how to reproduce it, and how to fix it step-by-step — just like we’d do in a live environment.

What Does ORA-00059 Mean?

Oracle uses a parameter called DB_FILES to define the maximum number of datafiles allowed in a database. When this limit is hit — typically during tablespace expansion — you’ll get the ORA-00059 error.

Quick Definition:

• DB_FILES: An initialization parameter that sets the upper limit of datafiles Oracle can handle.
• Default value: Often around 200.
• Type: Static (requires DB restart to change).

When Does This Error Occur?

Let’s say you run this:

ALTER TABLESPACE users ADD DATAFILE '/u01/app/oracle/oradata/users_10.dbf' SIZE 100M;

And Oracle responds with:

ORA-00059: maximum number of DB_FILES exceeded

This means you’ve already added as many datafiles as your DB_FILES setting allows.

How to Check and Reproduce the Error

Step 1: Check current file limit

SHOW PARAMETER db_files;

Sample output:

NAME      TYPE     VALUE
--------- -------- -----
db_files  integer  200

Step 2: Count your current datafiles

SELECT COUNT(*) FROM v$datafile;

If the count is equal to or greater than the db_files value, then you’ve hit the cap.

How to Fix ORA-00059 (Step-by-Step)

Since DB_FILES is a static parameter, you’ll need to change it and restart the database.

If your DB uses SPFILE (most production systems do):

Run this command:

ALTER SYSTEM SET db_files = 500 SCOPE=SPFILE;

Tip: Pick a safe future-proof value. 500 or 1000 is a common choice for growing systems.

If your DB uses PFILE (init.ora):

Manually edit the parameter file and set:

db_files=500

Restart the Database (Required)

Because DB_FILES is static, the change only takes effect after a warm restart.

SHUTDOWN IMMEDIATE;
STARTUP;

Test After Fixing

After the restart, recheck:

SHOW PARAMETER db_files;

Now re-run your original command:

ALTER TABLESPACE users ADD DATAFILE '/u01/app/oracle/oradata/users_10.dbf' SIZE 100M;

It should now work without errors.

💡 Pro Tips (Optional but Useful)

• List all current datafiles: sqlCopyEditSELECT file_name FROM dba_data_files ORDER BY tablespace_name;
• To reduce the number of datafiles needed, consider using BIGFILE tablespaces: sqlCopyEditCREATE BIGFILE TABLESPACE big_ts DATAFILE '/u01/oracle/big_ts01.dbf' SIZE 10G AUTOEXTEND ON;
• Always plan for some buffer in your db_files value — especially on partition-heavy or fast-growing databases.

Summary

That’s it! This fix is quick and permanent once applied. Make sure to monitor growth so you don’t hit this silently again.