ORA-00059: maximum no. of DB_FILES exceeded

ORA-00059:	maximum number of DB_FILES exceeded
Cause:	The value of the DB_FILES initialization parameter was exceeded.
Action:	Increase the value of the DB_FILES parameter and warm start.

Today I came across ORA00059: maximum number of DB_FILES exceeded, while adding a tablespace to my database then I checked db_files parameter

This will give the MAXDATAFILES specified at control file level while creating the database

Select   value  from  v$parameter   where  name = ‘db_files’ ;

value

-------

500

Also I checked  v$control_record_section to see MAXDATAFILES 

select records_total from v$controlfile_record_section where type = ‘DATAFILE’;

records_total

----------------

700

Then I increased the db_files parameter

SQL> alter system set db_files=700 scope=spfile;

System altered.

And bounce the database to see the new value

select   value  from  v$parameter   where  name = ‘db_files’;

value

-------

700

You can still increase db_files to morethan control_record_section parameter

EX:  db_files= 800

      Control_record_section=700

But when you reach datafiles to near 700 then control_record_section automatically increases its value

Ex: select count(*) from dba_data_files;

Count(*)

------------

698

And still if you add one more to it

Ex: select count(*) from dba_data_files;

Count(*)

------------

699

Then you can check the control_record_section  parameter automatically increases its value

select records_total from v$controlfile_record_section where type = ‘DATAFILE’ ;

records_total

------------------

800

NOTE:

When you issue CREATE DATABASE or CREATE CONTROLFILE statements, the MAXDATAFILES parameter specifies an initial size of the datafile portion of the control file. However,if you attempt to add a new file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, the control file will expand automatically so that the datafiles section can accommodate more files.

DB_FILES specifies the maximum number of database files that can be opened for this database.