A technical troubleshooting blog about Oracle with other Databases & Cloud Technologies.

“ORA-00059: Maximum Number Of DB_FILES Exceeded”

1 min read
electrician fixing an opened switchboard

Photo by Pixabay on Pexels.com

CAUSE

ALTER TABLESPACE TB1 add datafile '+WHALE_DG1' size 10g autoextend on next 500M
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded
We do not have to recreate the CONTROL FILE from 10g onwards to change/modify MAXLOGFILE, MAXLOGMEMBERS,MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES

For MAXDATAFILES, follow the steps below:
1] Determine the value of db_files

SQL> show parameter db_files

2] Check the number of Datafiles

SQL> select count(*) from dba_data_files;

3] Set the db_files parameter to a new high value

SQL> alter system set db_files=x scope=spfile;
4] Shutdown the database with immediate option and restart so that the new value for db_files can be read.

SQL> shutdown immediate

SQL> startup
5] Check the new value for db_files parameter

SQL> show parameter db_files

6] Now add the datafile and this time, the error ORA-00059 will not occur

SQL> alter tablespace <tablespace_name> add datafile '<Datafile_location>' size <size>;