While adding new datafile, you may face below error.

Problem:

ALTER TABLESPACE TS_LOYALTY_DATA add datafile ‘+DATA’ size 10g auto extend on next 500M
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded

 

Solution:

Check the db_files parameter in database

SQL> show parameter db_files

db_files integer 200

— Check the total number of datafiles present:

SQL> select count(*) from dba_data_files;

200

As you can see the total number of datafiles already reached the db_files parameter value set in a database.

update db_files parameter and restart the database

SQL> alter system set db_files=5000 scope=spfile sid=’*’;

System altered.

oracle@sec75-2:~/bsstdba$ srvctl stop database -d ULAPROD
oracle@sec75-2:~/bsstdba$ srvctl start database -d ULAPROD
oracle@sec75-2:~/bsstdba$ s

SQL*Plus: Release 12.1.0.2.0 Production on Wed Apr 20 09:58:53 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> show parameter db_files

NAME TYPE VALUE
———————————— ———– ——————————
db_files integer 5000

 

 

About The Author

Leave a Reply