“ORA-00059: Maximum Number Of DB_FILES Exceeded”

by 
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
2
3
1] Determine the value of db_files
 
SQL> show parameter db_files
1
2
3
2] Check the number of Datafiles
 
SQL> select count(*) from dba_data_files;
1
2
3
3] Set the db_files parameter to a new high value
 
SQL> alter system set db_files=x scope=spfile;
1
2
3
4
5
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
1
2
3
5] Check the new value for db_files parameter
 
SQL> show parameter db_files
1
2
3
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>;