ORA-00059: maximum number of DB_FILES exceeded - Fix / Resolve Oracle Add Datafile Error


Error Description: Create tablespace, add data file command failed with following error.

SQL> CREATE TABLESPACE TEST DATAFILE '/u01/app/oracle/oradata/testdb/TEST01.dbf' size 50m;
CREATE TABLESPACE TEST DATAFILE '/u01/app/oracle/oradata/testdb/TEST01.dbf' size 50m
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded


Solution Description:
When you get this error you have to check the value of DB_FILES parameter and the number of datafiles currently present in the database. You cannot add datafiles more than the value mentioned for the parameter DB_FILES. You have to increase the value for the parameter DB_FILES. In below example I set the value for DB_FILES to 9.

SQL> show parameter db_files;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
db_files                             integer     9

SQL> select count(*) from dba_data_files;

  COUNT(*)
----------
         9
SQL> CREATE TABLESPACE TEST DATAFILE '/u01/app/oracle/oradata/testdb/TEST01.dbf' size 50m;
CREATE TABLESPACE TEST DATAFILE '/u01/app/oracle/oradata/testdb/TEST01.dbf' size 50m
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded

How to set / increase the value for DB_FILES parameter?
  1. If you are using pfile/initfile change the parameter value for the DB_FILES and bounce the database.
  2. If you are using spfile use the following method to set the value for DB_FILES parameter.
SQL> ALTER system SET db_files=150 scope = BOTH;
SQL> shutdown immediate
SQL> startup
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google