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

advertisements

_____________________________________________________________________________________________________________________

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

_____________________________________________________________________________________________________________________

Website Stats