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