ORA-00059:maximum number of DB_FILES exceeded

ORA-00059:maximum number of DB_FILES exceeded

Error:
SQL> alter tablespace users add datafile ‘D:\Oracle\ORADATA\ORCL\User17.dbf’ size 100m ;
alter tablespace users add datafile ‘D:\APP1\SRIRAM\ORADATA\ORCL\User17.dbf’ size 100m
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded

Solution:
Increase the value of parameter DB_FILES in Oracle. For change this value you need to reboot the Oracle database.

1. Check the current value of db_files.

SQL> show parameter db_files
NAME TYPE VALUE
------------ ----------- -------
db_files integer 200

2. Increase the value of parameter.

alter system set db_files=500 scope=spfile;

3. Check the changed value of db_files.

SQL> show parameter db_files
NAME TYPE VALUE
--------------- ----------- -----
db_files integer 500

In my case, my db_files is not updated, it show me the old value of db_files. alter system command is not working. It show same old result of 200 after reboot the database.
To overcome this problem and reflect the changes in db_files parameter. We need to check the spfile.

1. After alter and reboot same output is coming.

SQL> show parameter db_files
NAME TYPE VALUE
------------ ----------- -------
db_files integer 200

2. Created pfile from spfile.

create pfile='D:\init.txt' from spfile;

3. Check the pfile init.txt is defined with two parameter.

*.db_files=500
ORCL.db_files=200

4. My database is ORCL so it pick entry for my database is from ORCL name.

manually delete the orcl.db_files parameter from init.txt file and save it.

5. Shutdown the database

shutdown immediate

6. Create spfile from pfile i.e init.txt file which recently save after removing orcl.db_files entry.

create spfile from pfile='D:\init.txt';

7. Start the database.

startup

8. Check the db_file parameter.

SQL> show parameter db_files
NAME TYPE VALUE
--------------- ----------- -----
db_files integer 500

Advertisements