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
Sriram Sanka - My Experiences with Databases & More 
Rod said
This post gives clear idea for the new users of blogging, that in fact how to do blogging and site-building.|
LikeLike
Gaylord said
Simply desire to say your article is as astonishing. The clearness to your submit is just great and i could assume you are a professional in this subject. Well together with your permission allow me to seize your feed to stay updated with coming near near post. Thanks 1,000,000 and please carry on the rewarding work.|
LikeLike
Hans said
Howdy! This post could not be written any better! Reading through this post reminds me of my old room mate! He always kept chatting about this. I will forward this post to him. Pretty sure he will have a good read. Many thanks for sharing!|
LikeLike
Ira said
Hey just wanted to give you a brief heads up and let you know a few of the images aren’t loading correctly. I’m not sure why but I think its a linking issue. I’ve tried it in two different internet browsers and both show the same results.|
LikeLike
Freeda said
Greate pieces. Keep posting such kind of info on your site. Im really impressed by it.
LikeLike
Loris said
Thank you, I have just been looking for info about this topic for a long time and yours is the greatest I’ve found out till now. However, what about the bottom line? Are you sure about the source?|
LikeLike
Philip said
Great site you have here.. It’s hard to find quality writing like yours these days. I honestly appreciate people like you! Take care!!|
LikeLike
Taren said
I like the helpful information you provide in your articles. I’ll bookmark your blog and check again here frequently. I am quite certain I will learn many new stuff right here! Best of luck for the next!|
LikeLike
Marty said
It’s really a great and useful piece of info. I’m satisfied that you simply shared this useful info with us. Please stay us up to date like this. Thanks for sharing.|
LikeLike
Odell said
wonderful post, very informative. I’m wondering why the other experts of this sector do not notice this. You should proceed your writing. I am sure, you’ve a great readers’ base already!|
LikeLike
Bradly said
That is really attention-grabbing, You are an excessively professional blogger. I’ve joined your feed and stay up for in search of extra of your magnificent post. Additionally, I’ve shared your site in my social networks|
LikeLike
Merrill said
I know this web site provides quality based content and other material, is there any other website which provides such information in quality?|
LikeLike
Marcy said
I think that is among the most significant information for me. And i am happy studying your article. However should statement on some basic things, The site taste is perfect, the articles is in reality great : D. Just right process, cheers|
LikeLike
Jed said
I was recommended this web site by my cousin. I am not sure whether this post is written by him as no one else know such detailed about my difficulty. You’re amazing! Thanks!|
LikeLike
Cathie said
I think what you published made a lot of sense. However, what about this? what if you added a little information? I mean, I don’t wish to tell you how to run your blog, but suppose you added something that grabbed people’s attention? I mean BLOG_TITLE is a little vanilla. You might peek at Yahoo’s home page and watch how they create article headlines to get viewers to open the links. You might add a related video or a pic or two to grab readers excited about what you’ve got to say. In my opinion, it could bring your posts a little livelier.|
LikeLike
Avery said
After looking over a handful of the articles on your blog, I truly appreciate your technique of blogging. I book-marked it to my bookmark website list and will be checking back soon. Please visit my website as well and tell me your opinion.|
LikeLike
Gilbert said
bookmarked!!, I love your blog!|
LikeLike
Royce said
This post is in fact a good one it assists new web visitors, who are wishing in favor of blogging.|
LikeLike
Neil said
I believe what you composed was very reasonable. But, what about this? what if you were to write a killer headline? I mean, I don’t want to tell you how to run your blog, but what if you added something that makes people want more? I mean BLOG_TITLE is a little vanilla. You might look at Yahoo’s front page and note how they write post titles to grab people to click. You might add a related video or a related picture or two to get people interested about everything’ve got to say. In my opinion, it could make your posts a little bit more interesting.|
LikeLike
Alan said
Good post. I learn something totally new and challenging on websites I stumbleupon on a daily basis. It’s always interesting to read through content from other writers and practice a little something from other sites. |
LikeLike
Larraine said
Right here is the right site for everyone who hopes to understand this topic. You know so much its almost hard to argue with you (not that I actually would want to…HaHa). You certainly put a new spin on a subject that has been written about for years. Wonderful stuff, just excellent!|
LikeLike
Jeanetta said
Really when someone doesn’t understand afterward its up to other people that they will assist, so here it takes place.|
LikeLike
Rafael said
Its such as you read my thoughts! You appear to grasp a lot approximately this, such as you wrote the book in it or something. I think that you could do with some percent to pressure the message home a bit, however instead of that, that is magnificent blog. An excellent read. I’ll definitely be back.|
LikeLike
Doyle said
Hi there! This blog post couldn’t be written much better! Looking through this article reminds me of my previous roommate! He continually kept talking about this. I most certainly will send this information to him. Fairly certain he’s going to have a very good read. Many thanks for sharing!|
LikeLike
Julee said
Write more, thats all I have to say. Literally, it seems as though you relied on the video to make your point. You definitely know what youre talking about, why waste your intelligence on just posting videos to your weblog when you could be giving us something informative to read?|
LikeLike
Enid said
We’re a gaggle of volunteers and starting a brand new scheme in our community. Your site provided us with helpful info to work on. You’ve done an impressive activity and our whole community shall be thankful to you.|
LikeLike
Blaine said
I loved as much as you will receive carried out right here. The sketch is tasteful, your authored subject matter stylish. nonetheless, you command get bought an shakiness over that you wish be delivering the following. unwell unquestionably come more formerly again since exactly the same nearly a lot often inside case you shield this hike.|
LikeLike
Kayleigh said
An impressive share! I have just forwarded this onto a co-worker who was conducting a little research on this. And he actually ordered me lunch due to the fact that I discovered it for him… lol. So allow me to reword this…. Thanks for the meal!! But yeah, thanks for spending the time to talk about this matter here on your website.|
LikeLike
Geri said
Hi there, this weekend is good in favor of me, as this moment i am reading this wonderful educational article here at my house.|
LikeLike
Shawn said
You have made some good points there. I checked on the internet to find out more about the issue and found most people will go along with your views on this web site.|
LikeLike
Lyle said
It is truly a nice and useful piece of information. I am glad that you just shared this helpful info with us. Please keep us informed like this. Thanks for sharing.|
LikeLike
Jarred said
Thank you a lot for sharing this with all of us you actually know what you’re speaking approximately! Bookmarked. Kindly additionally discuss with my site =). We could have a link change arrangement between us|
LikeLike
Ricarda said
I think this is among the most vital info for me. And i’m glad reading your article. But should remark on few general things, The website style is ideal, the articles is really great : D. Good job, cheers|
LikeLike
Deanna said
Hey very interesting blog!|
LikeLike
Gonzalo said
Hi there to all, how is everything, I think every one is getting more from this web page, and your views are nice in support of new visitors.|
LikeLike
Stacy said
excellent points altogether, you simply gained a emblem new reader. What could you suggest in regards to your post that you made some days in the past? Any certain?|
LikeLike
Miriam said
This is the right blog for everyone who would like to find out about this topic. You know so much its almost hard to argue with you (not that I actually will need to…HaHa). You certainly put a brand new spin on a subject which has been written about for ages. Great stuff, just great!|
LikeLike
Shila said
No matter if some one searches for his required thing, so he/she wants to be available that in detail, so that thing is maintained over here.|
LikeLike
Alejandro Laselle said
Wow, marvelous blog layout! How long have you been blogging for? you make blogging look easy. The overall look of your website is great, as well as the content!|
LikeLike
stewart said
Valuable information. Lucky me I discovered your website unintentionally, and I’m shocked why
this twist of fate didn’t took place earlier! I bookmarked it.
LikeLike
Chong Heilman said
Hello terrific blog! Does running a blog such as this take a massive amount work? I’ve no knowledge of coding but I was hoping to start my own blog soon. Anyways, if you have any suggestions or tips for new blog owners please share. I understand this is off subject nevertheless I just wanted to ask. Thanks a lot!
LikeLike
Adrian Kaase said
Very good article. I am experiencing many of these issues as well..
LikeLike
Myles Tatsapaugh said
Wonderful blog! I found it while searching on Yahoo News. Do you have any suggestions on how to get listed in Yahoo News? I’ve been trying for a while but I never seem to get there! Thanks
LikeLike
Car Wreckers said
But wanna comment on few general things, The website style is perfect, the subject material is really wonderful : D.
LikeLike
Cash for Cars said
But wanna input that you have a very nice site, I enjoy the pattern it really stands out.
LikeLike