ORA-00059: maximum number of DB_FILES exceeded

Posted by sriram 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:

  1. You can add datafiles to traditional smallfile tablespaces, subject to the following limitations:
  2. 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.
  3. Operating systems impose limits on the number and size of datafiles.
  4. 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

Advertisements

96 Responses to “ORA-00059: maximum number of DB_FILES exceeded”

  1. Rod said

    This post gives clear idea for the new users of blogging, that in fact how to do blogging and site-building.|

  2. 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.|

  3. 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!|

  4. 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.|

  5. Freeda said

    Greate pieces. Keep posting such kind of info on your site. Im really impressed by it.

  6. 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?|

  7. 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!!|

  8. 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!|

  9. 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.|

  10. 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!|

  11. 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|

  12. 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?|

  13. 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|

  14. 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!|

  15. 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.|

  16. 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.|

  17. Gilbert said

    bookmarked!!, I love your blog!|

  18. Royce said

    This post is in fact a good one it assists new web visitors, who are wishing in favor of blogging.|

  19. 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.|

  20. 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. |

  21. 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!|

  22. Jeanetta said

    Really when someone doesn’t understand afterward its up to other people that they will assist, so here it takes place.|

  23. 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.|

  24. 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!|

  25. 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?|

  26. 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.|

  27. 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.|

  28. 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.|

  29. 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.|

  30. 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.|

  31. 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.|

  32. 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|

  33. 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|

  34. Deanna said

    Hey very interesting blog!|

  35. 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.|

  36. 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?|

  37. 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!|

  38. 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.|

  39. 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!|

  40. 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.

  41. 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!

  42. Very good article. I am experiencing many of these issues as well..

  43. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Puddintopia

The Ramblings and Ravings Of Author Jason A. Rust

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

flashdba

Oracle databases, storage and the high-performance world of flash memory

jarneil

The thoughts of Jason Arneil

Frits Hoogland Weblog

IT Technology; Oracle, linux, TCP/IP and other stuff I find interesting

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

Martins Blog

Trying to explain complex things in simple terms

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Talip Hakan Ozturk's ORACLE BLOG

The secret of success is at your fingertips!...

Martin Widlake's Yet Another Oracle Blog

Oracle performance, Oracle statistics and VLDBs

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Oracle Scratchpad

Just another Oracle weblog

Oracle DBA Blog

Procedures, learnings and fixes encountered by a Junior DBA

Oracle ACE Director Kai Yu's Oracle Blog

Share Oracle Tips and Tricks and Related News

Online Identity & Access Management

One Stop Shop for Identity & Access Management

Uwe Hesse

about Database Technology

Oracle database internals by Riyaj

Discussions about Oracle performance tuning, RAC, Oracle internal & E-business suite.

Support

WordPress.com Support

Kamran Agayev's Oracle Blog

Oracle Certified Master

%d bloggers like this: