ORA-00059: Maximum Number Of DB_FILES Exceeded

Known issues:
When using Pluggable Database (PDB)/Container in 12c, create and drop of a single PDB might repeatedly fail with ORA-59.
If ORA-59 error is reported even when the total number of files is less than the value of db_files, then this could be due to Bug 17461374.
Download and apply the interim patch if it exists for your platform. No workaround exists for this bug.
Bug 17461374 – Create and drop of a single PDB repeatly fails with ORA-59 (Doc ID 17461374.8)
oracle support

ALTER TABLESPACE USERS add datafile '+DATA_USA' size 10G
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded

Table of Contents

Precautions

Indeed, not a regular issues you will face. But this can cause Production downtime for you in Production hours. This is error will occur when try to add a new datafile when your total number of datafiles in the database equals to DB_FILES parameter. DB_FILES has default value as 200 if you haven’t mentioned in your pfile. It will pick it as 200.

It’s always better to keep an eye on how many datafiles are there in your database and what is the value for DB_FILES parameter. Read about how to add datafile in Oracle database where I have tried to include all the basic precautions once should look at to avoid unplanned downtimes. Basic Query which can be used –

select (select count(1) from v$datafile) No_of_Data_files, (select value from v$parameter where upper(name) like '%DB_FILES%') Max_No_of_data_files from dual;

Of course you can right it as per your need. You can set an alert where database where it will check for the difference, if it goes below certain value, email is triggered to your team.

Secondly, whenever you are changing any parameter on production, make sure you change it in all DRs you have in your environment for the database. So whenever you change DB_FILES in production make sure you are changing it on it’s Standby databases.

Step by Step How to Resolve ORA-00059: Maximum Number Of DB_FILES Exceeded

To Resolve this issue, you require database downtime. Better to have alert in place.
oracledbworld.com

Cause –

Maximum number of datafiles specified via parameter db_files has been reached

DB_FILES is Hard Limit

DB_FILES is the hard limit, this define how many datafile will be allowed to be added in the database.

Check for DB_FILES value

SQL> show parameter db_files

Check Value for Total number of datafiles in Database

 select count(*) from dba_data_files;

Set the db_files parameter to a new high value

alter system set db_files=<higher value> scope=spfile;

for example - 
DB_FILES was 200 in my database to set it to 1000 I will fire following command - 

alter system set db_files=1000 scope=spfile;

In RAC this parameter forces you to bounce complete database. So node by node bounce is NOT Allowed

Restart your database

SQL>shutdown immediate;
SQL>startup

or 
srvctl stop database -d <db_unique_name>
srvctl start database -d <db_unique_name>

Check the new value for db_files parameter

SQL> show parameter db_files

Now you can add new datafile in tablespace. Hope this helps you

Reference – “ORA-00059: Maximum Number Of DB_FILES Exceeded” (Doc ID 1589201.1)