ORA-00059: maximum number of DB_FILES exceeded
Posted by Sriram Sanka on May 27, 2011
This is all about the Relation B/W MAXDATAFILES and DB_FILES In Oralce Database.
–This will Give Us db_files value. max no of data files that we can add.
select value from v$parameter where name = ‘db_files’;
This will give the MAXDATAFILES specified at Control file level while creating Database.
select records_total from v$controlfile_record_section where type = ‘DATAFILE’;
Lets Start with a small case. I have Created My database with DB_FILES as 10.
C:\Windows\System32>sqlplus system/tejajun20
SQL*Plus: Release 11.2.0.1.0 Production on Fri May 27 20:20:19 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select value from v$parameter where name = 'db_files';
VALUE
--------------------------------------------------------------------------------
10
SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';
RECORDS_TOTAL
-------------
100
Let us have a look at the no of data files I have.
SQL> select file_name from dba_data_files; FILE_NAME -------------------------------------------------------------------------------- D:\APP1\SRIRAM\ORADATA\ORAFAQ\USERS01.DBF D:\APP1\SRIRAM\ORADATA\ORAFAQ\UNDOTBS01.DBF D:\APP1\SRIRAM\ORADATA\ORAFAQ\SYSAUX01.DBF D:\APP1\SRIRAM\ORADATA\ORAFAQ\SYSTEM01.DBF D:\APP1\SRIRAM\ORADATA\ORAFAQ\EXAMPLE01.DBF</pre>
Let me add some data files to users tables
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User2.dbf' size 100m ;
Tablespace altered.
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User3.dbf' size 100m ;
Tablespace altered.
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User4.dbf' size 100m ;
Tablespace altered.
SQL> select count(*) from v$datafile;
COUNT(*)
----------
8
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User5.dbf' size 100m ;
Tablespace altered.
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User6.dbf' size 100m ;
Tablespace altered.
SQL> select count(*) from v$datafile;
COUNT(*)
----------
10
SQL> select value from v$parameter where name = 'db_files';
VALUE
--------------------------------------------------------------------------------
10
SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';
RECORDS_TOTAL
-------------
100
As I have already reached the max limit 10,Adding one more file will raise an Error.
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User7.dbf' size 100m ; alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User7.dbf' size 100m * ERROR at line 1: ORA-00059: maximum number of DB_FILES exceeded
ORA-00059:
maximum number of DB_FILES exceeded Cause: The value of the DB_FILES initialization parameter was exceeded. Action: Increase the value of the DB_FILES parameter and warm start.
Lets Try to increase the value to a small number.
SQL> alter system set db_files=20 scope=spfile;
System altered.
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 401743872 bytes
Fixed Size 1374892 bytes
Variable Size 268436820 bytes
Database Buffers 125829120 bytes
Redo Buffers 6103040 bytes
Database mounted.
Database opened.
SQL> select value from v$parameter where name = 'db_files';
VALUE
--------------------------------------------------------------------------------
20
SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';
RECORDS_TOTAL
-------------
100
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User7.dbf' size 100m ;
Tablespace altered.
SQL>
See now it allowing us to add datafiles.what about Exceeding MAXDATAFILES? (i.e more than 100 data files).
Lets do that And verify what `ll happen.
SQL> alter system set db_files=150 scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 401743872 bytes
Fixed Size 1374892 bytes
Variable Size 268436820 bytes
Database Buffers 125829120 bytes
Redo Buffers 6103040 bytes
Database mounted.
Database opened.
SQL> select value from v$parameter where name = 'db_files';
VALUE
--------------------------------------------------------------------------------
150
SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';
RECORDS_TOTAL
-------------
100
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User8.dbf' size 1m ;
Tablespace altered.
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User9.dbf' size 1m ;
Tablespace altered.
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User10.dbf' size 1m ;
Tablespace altered.
SQL> select count(*) from v$datafile;
COUNT(*)
----------
14
--- In another window I am adding datafiles upto the limit "100"
SQL> /
COUNT(*)
----------
48
SQL> /
COUNT(*)
----------
55
SQL> /
COUNT(*)
----------
82
SQL> /
COUNT(*)
----------
98
SQL> /
COUNT(*)
----------
98
SQL> /
COUNT(*)
----------
98
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User95.dbf' size 1m ;
Tablespace altered.
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User96.dbf' size 1m ;
Tablespace altered.
SQL>
SQL> select value from v$parameter where name = 'db_files';
VALUE
--------------------------------------------------------------------------------
150
SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';
RECORDS_TOTAL
-------------
100
SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User97.dbf' size 1m ;
Tablespace altered.
SQL> select value from v$parameter where name = 'db_files';
VALUE
--------------------------------------------------------------------------------
150
SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';
RECORDS_TOTAL
-------------
200
----Observe it Automatically changed..
SQL>
As Per Oracle Documents :
Consider Possible Limitations When Adding Datafiles to a Tablespace:
- You can add datafiles to traditional smallfile tablespaces, subject to the following limitations:
- Operating systems often impose a limit on the number of files a process can open simultaneously. More datafiles cannot be created when the operating system limit of open files is reached.
- Operating systems impose limits on the number and size of datafiles.
- The database imposes a maximum limit on the number of datafiles for any Oracle Database opened by any instance. This limit is operating system specific.
You cannot exceed the number of datafiles specified by the DB_FILES initialization parameter.
When you issue CREATE DATABASE or CREATE CONTROLFILE statements, the MAXDATAFILES parameter specifies an initial size of the datafile portion of the control file. However,if you attempt to add a new file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, the control file will expand automatically so that the datafiles section can accommodate more files.
DB_FILES specifies the maximum number of database files that can be opened for this database.The maximum valid value is the maximum number of files, subject to operating system constraint,that will ever be specified for the database, including files to be added by ADD DATAFILE statements.If you increase the value of DB_FILES, then you must shut down and restart all instances accessing the database before the new value can take effect. If you have a primary and standby database,then they should have the same value for this parameter.
For More Information Please Read:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dfiles.htm#sthref1343
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dfiles.htm#sthref1343
Not Just Databases 
Richard Boon said
very use full to me, since i faced this same issue now and solved it by seeing thi doc,
Thanks a lot very much.
Regards,
Richard Boon
+91 9941309968
SAP Basis Administrator
LikeLike
sriram said
Thank you ,Richard Boon
-Sriram
LikeLike
Neeraj said
very good Anaysis indeed….
Keep working good work…
goc Mless you 🙂
LikeLike
sriram said
Thank you Neeraj 🙂
LikeLike
shallow said
I’m not that much of a internet reader to be honest but your blogs really nice, keep it up!
I’ll go ahead and bookmark your website to come back later. All the best
LikeLike
bosspy review said
I do consider all of the ideas you have presented to your post.
They’re very convincing and can definitely work. Nonetheless, the posts are
very brief for novices. May just you please lengthen them a little
from ext time? Thaank you forr the post.
LikeLike
google said
Simply want to say your article is as astonishing. The clearness in
your post is simply nice and i can assume
you are an expert on this subject. Fine with your permission allow me to grab your feed to keep updated with forthcoming post.
Thanks a million and please carry on the rewarding
work.
LikeLike
asymptotix.eu said
Hi my family member! I wish to say that this article is amazing, great written and include approximately all important infos.
I’d like to peer extra posts like this .
LikeLike
will said
I believe what you said made a great deal of sense.
However, think about this, suppose you typed a catchier title?
I mean, I don’t want to tell you how to run your blog, but suppose you added a post title to possibly get people’s attention?
I mean ORA-00059: maximum number of DB_FILES exceeded is kinda vanilla.
You could look at Yahoo’s home page and note how they write article headlines
to get viewers to open the links. You might add a video or
a pic or two to grab readers interested about everything’ve
got to say. In my opinion, it would bring your blog a little bit more interesting.
LikeLike
gas grill reviews said
I have read so many articles about the blogger lovers but this paragraph is actually a fastidious
post, keep it up.
LikeLike
non permanent hair color said
I’m really impressed with your writing skills as well as with the layout on your weblog.
Is this a paid theme or did you customize it yourself?
Anyway keep up the excellent quality writing, it is rare to see
a great blog like this one nowadays.
LikeLike
fifa 14 key generator download said
Hurrah! Finally I got a web site from where I know
how to truly get helpful facts regarding my study and knowledge.
LikeLike
telecharger gravity master said
I believe what you posted made a lot of sense.
However, consider this, suppose you composed a catchier post
title? I mean, I don’t wish to tell you how to run your blog, however suppose you added a post title that grabbed folk’s attention?
I mean ORA-00059: maximum number of DB_FILES exceeded is a little plain.
You ought to glance at Yahoo’s front page and watch how they write
news titles to grab viewers interested. You might try adding a video or a picture or two to get people excited about everything’ve written.
Just my opinion, it might make your posts a little livelier.
LikeLike
ORA-00059: maximum number of DB_FILES exceeded said
Travis Zweig
This is all about the Relation B/W MAXDATAFILES and DB_FILES In Oralce Database. –This will Give Us db_files value. max no of data files that we can add. select value from v$parameter where name =
LikeLike
HP Care Pack said
Good article. I absolutely appreciate this website.
Keep it up!
LikeLike
Colene Andere said
hello dudes!! Brilliant site!
LikeLike
turmeric benefits for skin said
Great article, just what I wanted to find.
LikeLike
Orange County Employment Agency said
Hey! This is my first comment here so I just wanted to give a quick
shout oout and tell you I genuinely enjoy reading through your posts.
Can you recommend any othr blogs/websites/forums that cover thee
same topics? Thanks a lot!
LikeLike
MLSP said
If people connect with oneself on a person level, they may be extra prone to
would like to take into consideration your firm.
LikeLike
squirt said
Have you ever thought about including a little bit more than just your articles?
I mean, what you say is fundamental and everything. Nevertheless think about if you added some great photos or video clips to give your posts more, “pop”!
Your content is excellent but with images and videos, this blog could definitely be
one of the greatest in its niche. Amazing blog!
LikeLike
http://extralams.eu/ said
I’m really enjoying the design and layout of your blog.
It’s a very easy on the eyes which makes it much more enjoyable
for me to come here and visit more often. Did you hire out a
designer to create your theme? Superb work!
LikeLike
Gennie said
I tгuly love yоur site.. Pleasnt colors & theme. Ɗid уou develop this amazing
site ʏourself? Pleаse reply bɑck as I’m tryijg to create mү own personal blog and would like to learn wherе yoou got thіs from oг exsctly աhat the theme iѕ named.
Cheers!
LikeLike
http://www.mailnmore-ht.com/__media__/js/netsoltrademark.php?d=freeitunecodes.com said
Gгeat informаtion. Lucky me І camе acrօss
ʏoսr blog Ƅy accidesnt (stumbleupon). I have saved ass a favorite
fοr lɑter!
LikeLike
Laverne said
Hello there! This post couldn’t be written much better!
Looking through this article reminds me of my previous roommate!
He always kept talking about this. I am going to send this
information to him. Fairly certain he’s going to have a great read.
Many thanks for sharing!
LikeLike
Plants vs Zombies said
This text is worth everyone’s attention. Where can I find out more?
LikeLike
samsung 50 inch Led tv said
Oh my goodness! Impressive article dude! Thanks, However I am
going through troubles with your RSS. I don’t understand why I am unable
to join it. Is there anyone else having similar RSS issues?
Anyone who knows the solution can you kindly
respond? Thanx!!
LikeLike
aptoide said
Thanks for any other informative website. Where else may just
I get that type of information written in such an ideal
method? I’ve a challenge that I am just now running on, and I’ve been at the look out for such info.
LikeLike
clash of clans gem hack said
Yoou made sоme decent pоints tҺere. I checked оn the web for more info
aЬоut the issue аnd found most individuals wil go long with
youг views oon thіs website.
LikeLike
epson printer repair said
I’m not sure where you’re getting your info, but great topic.
I needs to spend some time learning much more or understanding more.
Thanks for great information I was looking for this info for my mission.
LikeLike
Lee Kinsey LPC said
I believe this is among the so much significant information for
me. And i am glad reading your article. However should statement on some normal things, The site taste is wonderful, the
articles is in reality great : D. Excellent process, cheers
LikeLike
http://www.BLUMENTHALART.COM said
I dugg ѕome of you post as I cereЬrated thеy were very bеeneficial handy
LikeLike
http://www.appsourcecodeforsale.com/activity/p/686953/ said
Tremendous things here. I’m very happy to see
your article. Thanks a lot and I’m having a look ahead to touch
you. Will you please drop me a mail?
LikeLike
http://itmarinecorps.altervista.org/index.php?mod=users&action=view&id=37880 said
Very nice post. I just stumbled upon your blog and wanted to say that I have really
enjoyed surfing around your blog posts. After all I will be subscribing
to your rss feed and I hope you write again very soon!
LikeLike
Ganesh@ said
Very nice article sir 🙂
Keep up the good work 🙂
LikeLike
Sri Ram said
Hi SriRam
Thank you this blog has given sufficient information how to change the DB_FILES parameter in single instance. Can you please let me the process of same in RAC environment. Do we need to change this in every instance? If possible plz share the steps in details
SQL> select name ||’:’||host_name name, instance_name from gv$database, gv$instance;SQL>
NAME INSTANCE_NAME
————————- —————-
ZFA01P:sl04623 ZFA01P1
ZFA01P:sl04623 ZFA01P1
ZFA01P:sl04624 ZFA01P2
ZFA01P:sl04624 ZFA01P2
Thank you in advance.
LikeLike
nivi said
really very good description thank you seems yu are a nuts and bolt dba boss
LikeLike
Nannette Declue said
I am regular reader, how are you everybody? This article posted at this site is in fact fastidious.|
LikeLike
Sbobet said
Have you ever considered about adding a little bit more than just your
articles? I mean, what you say is important and all. But just imagine if you added
some great photos or videos to give your posts more, “pop”!
Your content is excellent but with pics and video clips, this site could certainly be one of the best in its field.
Wonderful blog!
LikeLike
Felix Streit said
Thank you for sharing superb informations. Your site is so cool. I am impressed by the details that you’ve on this site. It reveals how nicely you perceive this subject. Bookmarked this website page, will come back for extra articles. You, my friend, ROCK! I found just the information I already searched all over the place and just could not come across. What a great web-site.
LikeLike
Davina Lompa said
Excellent read, I just passed this onto a colleague who was doing a little research on that. And he just bought me lunch since I found it for him smile Therefore let me rephrase that: Thanks for lunch!
LikeLike
Lyndon Gillice said
Absolutely written articles, Really enjoyed examining.
LikeLike
Ervin Caddy said
This is really interesting, You’re a very skilled blogger. I’ve joined your rss feed and look forward to seeking more of your great post. Also, I have shared your web site in my social networks!|
LikeLike
CASINO said
CASINO
ORA-00059: maximum number of DB_FILES exceeded «
LikeLike
Phil Dandrade said
“Great Blogpost! I couldn’t refrain from commenting. _Perfectly written!”
LikeLike
Sebrina said
Very quickly this web site will be famous amid all blogging visitors, due to it’s fastidious content|
LikeLike
Suzi said
Way cool! Some extremely valid points! I appreciate you writing this write-up and the rest of the site is extremely good.|
LikeLike
Lauryn said
I go to see daily a few sites and information sites to read articles or reviews, but this webpage provides quality based content.|
LikeLike
Edwin said
I am sure this piece of writing has touched all the internet viewers, its really really good piece of writing on building up new blog.|
LikeLike
Alecia said
Wonderful post! We will be linking to this great content on our site. Keep up the good writing.|
LikeLike
Herb said
Can you tell us more about this? I’d love to find out more details.|
LikeLike
Domingo said
It’s an awesome piece of writing designed for all the online people; they will get advantage from it I am sure.|
LikeLike
Taina said
Thanks to my father who shared with me concerning this webpage, this blog is genuinely amazing.|
LikeLike
Hong said
I am regular visitor, how are you everybody? This paragraph posted at this website is genuinely pleasant.|
LikeLike