ORA-00059 :  maximum number of DB_FILES exceeded

alter tablespace anuj add datafile '+DATA' size 1G autoextend on maxsize unlimited; 

*

ERROR at line 1:

ORA-00059: maximum number 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 restart database.

MAXDATAFILES and DB_FILES Parameters (Doc ID 119507.1)

 ORACLE 8i AND ABOVE   

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

For Oracle 8i and higher, MAXDATAFILES parameter of the control file will expand automatically upto the db_files parameter.

For example, if MAXDATAFILES is set to 32 and DB_FILES is set to 40, then, when a new file

is added, then, the MAXDATAFILE will increase in the control file automatically.

 

col name       for a15

col par_value  for a12

select name,par_value,records_total,records_used, par_value - records_used remain_value from (  select name,value par_value       from v$parameter where name in ('db_files','DB_FILE'))

                                                                                            ,(  select records_total,records_used from v$controlfile_record_section where type ='DATAFILE');

alter session set nls_Date_format='dd-mm-yyyy hh24:mi:ss'; 

set linesize 200 

col name      for a15

col par_value for a12

col db_name for a12

select  sysdate,sys_context ('USERENV', 'db_name') db_name,a.* from (select name,par_value,records_total,records_used, par_value - records_used remain_value from (select name,value par_value from v$parameter 

where upper(name) in ('DB_FILES')) ,(  select records_total,records_used from v$controlfile_record_section where type ='DATAFILE')) a

SYSDATE             DB_NAME      NAME            PAR_VALUE    RECORDS_TOTAL RECORDS_USED REMAIN_VALUE

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

09-03-2021 13:19:00 AAAPROD      db_files        200                   1024           54          146

 

set lines 200 pages 100 feedback off verify off

col type           for a30        HEADING 'Record Type'

col record_size    for 999999     HEADING 'Record|Size'

col records_used   for 999999     HEADING 'Records|Used'

col first_index    for 9999999    HEADING 'First|Index'

col last_index     for 9999999    HEADING 'Last|Index'

col last_recid     for 999999999  HEADING 'Last|Record|ID'

select

   type,

   record_size,

   records_total,

   records_used,

   first_index,

   last_index,

   last_recid

from v$controlfile_record_section

 where 1=1

 and type like 'DATA%'

 ;

 

                                                                                     Last

                                Record               Records    First     Last     Record

Record Type                       Size RECORDS_TOTAL    Used    Index    Index         ID

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

DATABASE                           316             1       1        0        0          0

DATAFILE                           520          1024      54        0        0      18304

DATAFILE COPY                      736          1000       0        0        0          0

DATABASE INCARNATION                56           292       2        1        2          2

DATAFILE HISTORY                   568            57       0        0        0          0

DATABASE BLOCK CORRUPTION           80          8384       0        0        0          0

                 

                      

show parameter db_file

NAME                                 TYPE        VALUE

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

db_files                             integer     200

for total no of file ..

select count(*) from v$datafile;

Change on Standby as well .. if you have a standby  !!!

alter system set db_files=[VALUE] scope=spfile sid='*';

alter system set db_files=400 SCOPE=spfile sid=’*’;

Restart the database !!!