Wednesday, September 30, 2009

How to: ORA-00059: maximum number of DB_FILES exceeded

Symptom:


create tablespace data_tbs38 datafile "+DATA' size 100M;
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded


Solution:

Increase db_files initialization parameter and bounce the database. Increase this parameter will incease the PGA consumation for every server process.


SQL> alter system set db_files=500 scope=spfile sid='*';

System altered.


How about MAXDATAFILES specified when creating database?

From Oracle8 onwards, Controlfile will auto-expend if MAXDATAFILES is smaller. similar inforamtion could be found in the alert log:


SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.0.1 - Beta
PL/SQL Release 11.2.0.0.1 - Beta
CORE 11.2.0.0.1 Beta
TNS for 32-bit Windows: Version 11.2.0.0.1 - Beta
NLSRTL Version 11.2.0.0.1 - Beta



SQL> select type,record_size rs, records_total rt, records_used ru,first_index f
2 last_index li, last_recid lr from v$controlfile_record_section;

TYPE RS RT RU FI LI LR
---------------------------- ------ -------- --- --- --- ---
DATABASE 316 1 1 0 0 0
CKPT PROGRESS 8180 11 0 0 0 0
REDO THREAD 256 8 1 0 0 0
REDO LOG 72 16 3 0 0 0
DATAFILE 520 52 23 0 0 17
FILENAME 524 2138 27 0 0 0
TABLESPACE 68 40 24 0 0 18
TEMPORARY FILENAME 56 20 1 0 0 1
RMAN CONFIGURATION 1108 50 2 0 0 2
LOG HISTORY 56 292 1 1 1 1
OFFLINE RANGE 200 81 0 0 0 0

TYPE RS RT RU FI LI LR
---------------------------- ------ -------- --- --- --- ---
ARCHIVED LOG 584 308 3 1 3 3
BACKUP SET 40 409 0 0 0 0
BACKUP PIECE 736 44 0 0 0 0
BACKUP DATAFILE 200 81 0 0 0 0
BACKUP REDOLOG 76 215 0 0 0 0
DATAFILE COPY 736 44 0 0 0 0
BACKUP CORRUPTION 44 371 0 0 0 0
COPY CORRUPTION 40 409 0 0 0 0
DELETED OBJECT 20 818 0 0 0 0
PROXY COPY 928 88 0 0 0 0
BACKUP SPFILE 124 131 0 0 0 0

TYPE RS RT RU FI LI LR
---------------------------- ------ -------- --- --- --- ---
DATABASE INCARNATION 56 292 1 1 1 1
FLASHBACK LOG 84 2048 0 0 0 0
RECOVERY DESTINATION 180 1 1 0 0 0
INSTANCE SPACE RESERVATION 28 1055 1 0 0 0
REMOVABLE RECOVERY FILES 32 1000 0 0 0 0
RMAN STATUS 116 141 0 0 0 0
THREAD INSTANCE NAME MAPPING 80 8 8 0 0 0
MTTR 100 8 1 0 0 0
DATAFILE HISTORY 568 57 0 0 0 0
STANDBY DATABASE MATRIX 400 31 31 0 0 0
GUARANTEED RESTORE POINT 212 2048 0 0 0 0

TYPE RS RT RU FI LI LR
---------------------------- ------ -------- --- --- --- ---
RESTORE POINT 212 2083 0 0 0 0
DATABASE BLOCK CORRUPTION 80 2045 0 0 0 0
ACM OPERATION 104 64 5 0 0 0
FOREIGN ARCHIVED LOG 604 1002 0 0 0 0

37 rows selected.


Expanded controlfile section 6 from 20 to 40 records
The number of logical blocks in section 6 remains the same
Completed: create tablespace test12 datafile size 10m
create tablespace test13 datafile size 10m
Expanded controlfile section 4 from 20 to 52 records
Requested to grow by 32 records; added 1 blocks of records