1 2 Previous Next 28 Replies Latest reply: Apr 9, 2012 9:22 PM by rp0428 RSS

    ORA-00059: maximum number of DB_FILES exceeded

    534640
      Hi All,
      I m getting the following error
      ORA-00059: maximum number of DB_FILES exceeded
      As data_files parameter reaches its maximum value.
      Can any one tell me how to change the value dynamically. Our 10G ver2 database is 24/7 operational. So i dont want to shutdown and restart. Is there any other solution to change the value.
      I tried to change the pfile value and then recreated spfile and again facing the same problem
      Thanks
        • 1. Re: ORA-00059: maximum number of DB_FILES exceeded
          248498
          You cannot change this parameter dynamically. You must restart your oracle instance in order this parameter takes effect.
          • 2. Re: ORA-00059: maximum number of DB_FILES exceeded
            Paul M.
            Unfortunately that's a static parameter, so you can't change it without restarting the DB. See http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams049.htm#sthref205
            • 3. Re: ORA-00059: maximum number of DB_FILES exceeded
              537005
              This parameter is in the CONTROL FILE not in pfile or spfile.
              You must recreate the control file.

              This is the only way...for me :)

              bye
              • 4. Re: ORA-00059: maximum number of DB_FILES exceeded
                248498
                You do not need to recreate control file. See below:

                SQL> sho user
                SYS
                SQL> select * from v$version;

                BANNER
                ----------------------------------------------------------------
                Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
                PL/SQL Release 10.2.0.2.0 - Production
                CORE 10.2.0.2.0 Production
                TNS for 32-bit Windows: Version 10.2.0.2.0 - Production
                NLSRTL Version 10.2.0.2.0 - Production

                SQL> sho parameter db_files

                NAME TYPE VALUE
                ------------------------------------ ----------- ------------------------------
                db_files integer 200
                SQL> alter system set db_files = 256 scope = spfile;

                SQL> shutdown immediate
                SQL> startup
                SQL> sho parameter db_files

                NAME TYPE VALUE
                ------------------------------------ ----------- ------------------------------
                db_files integer 256
                • 5. Re: ORA-00059: maximum number of DB_FILES exceeded
                  534640
                  Thanks all for quick support
                  • 6. Re: ORA-00059: maximum number of DB_FILES exceeded
                    Mohammed Taj
                    : maximum number of DB_FILES exceeded
                    i don't think here need to create control files.
                    bcoz OP is Ora-00059 err

                    we just need to increase this parameter
                    and startup our database
                    SQL> show parameter db_files

                    NAME                                 TYPE        VALUE
                    ------------------------------------ ----------- ------------------------------
                    db_files                             integer     6
                    SQL> create tablespace test
                      2  datafile 'd:\test01.dbf' size 1m;
                    create tablespace test
                    *
                    ERROR at line 1:
                    ORA-00059: maximum number of DB_FILES exceeded


                    SQL> shutdown immediate;
                    Database closed.
                    Database dismounted.
                    ORACLE instance shut down.
                    SQL> create pfile from spfile;

                    File created.

                    SQL> --change db_files parameter in init<db>.ora file
                    SQL> create spfile from pfile;

                    File created.

                    SQL> startup
                    ORACLE instance started.

                    Total System Global Area  171966464 bytes
                    Fixed Size                   787988 bytes
                    Variable Size             145488364 bytes
                    Database Buffers           25165824 bytes
                    Redo Buffers                 524288 bytes
                    Database mounted.
                    Database opened.
                    SQL> show parameter db_files

                    NAME                                 TYPE        VALUE
                    ------------------------------------ ----------- ------------------------------
                    db_files                             integer     200
                    SQL>
                    Note : @Paul . sir if this problem occur in 24x7 database enviorment then what is solution.
                    plz. reply
                    thanx
                    • 7. Re: ORA-00059: maximum number of DB_FILES exceeded
                      Satish Kandi
                      Sorry for interrupting, but in case of a production system, you would have already set DB_FILES to a calculated value (depending on size of database, expected growth etc). Even to some extent max possible value.

                      Considering the fact that this parameter is governed by the MAXDATAFILES used in the database creation, if set low, you need to recreate the controlfile and restart the database ;)

                      Thanks
                      • 8. Re: ORA-00059: maximum number of DB_FILES exceeded
                        Paul M.
                        if this problem occur in 24x7 database enviorment then what is solution.
                        No solution. You calculate it at DB creation, or you restart the DB.
                        • 9. Re: ORA-00059: maximum number of DB_FILES exceeded
                          Paul M.
                          Considering the fact that this parameter is governed
                          by the MAXDATAFILES used in the database creation, if
                          set low, you need to recreate the controlfile and
                          restart the database ;)
                          You don't need to recreate the controlfile. The following is from an Oracle article :

                          In Oracle8i and higher, if the MAXDATAFILES limit is reached the controlfile will expand automatically.

                          In Oracle 8i and higher, when you issue CREATE DATABASE or CREATE CONTROLFILE statements, the MAXDATAFILES parameter specifies an initial size of the datafile portion of the control file. Later, if you add a file whose number exceeds MAXDATAFILES but is less than or equal to the value specified by the DB_FILES initialization parameter, the control file automatically expands to allow the datafile portion to accommodate more files.

                          We can have DB_FILES set to more than the value of MAXDATAFILES.
                          • 10. Re: ORA-00059: maximum number of DB_FILES exceeded
                            537005
                            Thanks at all!

                            i dont know this feature, but i know only maxdatafiles paramater.

                            Bye
                            • 11. Re: ORA-00059: maximum number of DB_FILES exceeded
                              534640
                              Hello All,
                              I just edit the parameter db_files in pfile and restarted the instance and every thing comes to normal.
                              thanx
                              • 12. Re: ORA-00059: maximum number of DB_FILES exceeded
                                Satish Kandi
                                Hi Paul,

                                My statement was based on Oracle reference document of Oracle 8.1.7 only..

                                http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76961/ch138.htm#58406

                                >>
                                DB_FILES
                                Parameter type: Integer

                                Parameter class: Static

                                Default value: Operating system dependent

                                Range of values: Minimum: the current actual number of datafiles in the database

                                Maximum: the value that was specified in the MAXDATAFILES clause the last time CREATE DATABASE or CREATE CONTROLFILE was executed
                                >>

                                I actually have never faced this error as I have configured my databases with a MAX possible value on my system for MAXDATAFILES, that again based on above documentation only.

                                I guess Oracle document is contradicting in case of DB_FILES and MAXDATAFILES documentation.

                                Regards

                                Satish
                                • 13. Re: ORA-00059: maximum number of DB_FILES exceeded
                                  Paul M.
                                  Hi Satish,

                                  Nowadays is a bit dangerous to rely on 8.1.6 documentation...:-)

                                  I don't have an 8.1.7.4 on hands at the moment, but probably is was already different, and surely is different 9i, and 10g, from which comes the link I posted in my first post.
                                  • 14. Re: ORA-00059: maximum number of DB_FILES exceeded
                                    Satish Kandi
                                    Yes Paul...

                                    The documentation is definitely different and is changing. I just referred the 9i and 10GR1 documents that were with me and sure is different.

                                    I have some databases running on Oracle 8.1.7 (still)... so I will stick with Oracle 8i documentation for these databases at least.

                                    Anyways, I will keep myself updated now.

                                    Thanks.
                                    1 2 Previous Next