Search This Blog

Total Pageviews

Wednesday 17 May 2017

ORA-00059 : maximum number of DB_FILES exceeded

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 !!!


Oracle DBA

anuj blog Archive