DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: ORA-01118: cannot add any more database files: limit of 30 exceeded

  1. #1
    Join Date
    Sep 2000
    Posts
    78
    Hi,

    When I try to create datafile it says:

    ORA-01118: cannot add any more database files: limit of 30 exceeded

    but my DB_FILES parameter is set to 200. What might be the problem? Maybe there is some other parameter for this?

    Thanx

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by siroracle
    Hi,

    When I try to create datafile it says:

    ORA-01118: cannot add any more database files: limit of 30 exceeded

    but my DB_FILES parameter is set to 200. What might be the problem? Maybe there is some other parameter for this?

    Thanx
    When you run CREATE DATABASE, you often have a parameter called MAXDATAFILES. The value that is entered for this parameter or some default value is stored in the CONTROL FILE upon DB creation. This is the so called "hard" limit on the number of datafiles which can be associated with this particular database. Remember DB, not instance!

    You have hit the hard limit.

    In the init.ora you have DB_FILES. This is the limit on the total number of files associated with a particular INSTANCE of a database. As you know several instances can be associated with a single DB. This is called the "soft limit".

    The default for MAXDATAFILES is 30, you case, (Oracle 8 on UNIX) and your limit is 1022 per TS and 65536 per DB.

    When creating you DB, you may of course set MAXDATAFILES to the maximum. I, for example, set it to the year I was born in :-) Too high value makes the CF unnesessary big. On the other hand, if you set DB_FILES to a high number, it increases the PGA.

    You will have to recreate your control file if you want more than 30 DB files.

    Also, reduce DB_FILES, you are just increasing in vain the size of the PGA.


  3. #3
    Join Date
    Sep 2000
    Posts
    78
    Thanx! I will find info on how to recreate the CF, I hope I will not havce to recreate the DB as there is very little time for downtime for this DB.

    Thanx a lot! I have completely forgot of the MAXDATAFILES on CREATE DATABASE command as I use very rarely

  4. #4
    Join Date
    Sep 2001
    Posts
    200
    Hope this is not too late to help you.
    Julian is right. You have to recreate the controlfile to change that 'hard' value MAXDATAFILE. The simpliest way to do this is to do an ALTER DATABASE BACKUP CONTROLFILE TO TRACE. Then go to UDUMP dest, pick it up. Modify the value of MAXDATAFILES to what you want. you can delete all that junk before you get to STARTUP NOMOUNT. Do a clean shutdown. invoke SVRMGRL (make sure the file you edited is at this level). Then issue @(name-of-file). Make sure not to mess anything else in the controlfile.
    Hope this helps
    Tony
    Life is what is happening today while you were planning tomorrow.

  5. #5
    Join Date
    Sep 2001
    Posts
    200
    Hope this is not too late to help you.
    Julian is right. You have to recreate the controlfile to change that 'hard' value MAXDATAFILE. The simpliest way to do this is to do an ALTER DATABASE BACKUP CONTROLFILE TO TRACE. Then go to UDUMP dest, pick it up. Modify the value of MAXDATAFILES to what you want. you can delete all that junk before you get to STARTUP NOMOUNT. Do a clean shutdown. invoke SVRMGRL (make sure the file you edited is at this level). Then issue @(name-of-file). Make sure not to mess anything else in the controlfile..
    Hope this helps
    Tony
    ps or you can decide to shutdown the database first. Re-mount then do your ALTER DATABASE BACKUP CONTROFILE TO TRACE to avoid any calls for resetlogs.
    Life is what is happening today while you were planning tomorrow.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width