Max datafile allowed per tablespace?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Max datafile allowed per tablespace?

Hybrid View

  1. #1
    Join Date
    Jan 2001
    Location
    Vancouver, Canada
    Posts
    87
    Hi,

    I have a tablespace with 2000 tables and approximately 132GB of data. I am experiencing some performance issues and was wondering if there is limit on number of datafiles per tablespace by Oracle. I am using Oracle 8.1.7 and Oracle 8.0.6 on HP 11.0 64-bit Unix.

    Thanks in advance..

    Mohammad Zahid
    Software Developer
    Database Management Applications.
    Vancouver, Canada
    E-mail: mzahid@shaw.ca

  2. #2
    Join Date
    Feb 2002
    Location
    Dallas , Texas
    Posts
    158
    You can add datafiles to tablespaces, subject to the following operating system-specific datafile limits:

    Operating system limit

    Each operating system sets a limit on the maximum number of open files per process. Regardless of all other limits, more datafiles cannot be created when the operating system limit of open files is reached.

    Oracle system limit

    Oracle imposes a maximum limit on the number of datafiles for any Oracle database opened by any instance. This limit is port-specific.

    Control file upper bound

    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.

    Instance or SGA upper bound

    When starting an Oracle8 instance, the database's initialization parameter file indicates the amount of SGA space to reserve for datafile information; the maximum number of datafiles is controlled by the DB_FILES initialization parameter. This limit applies only for the life of the instance.


    --------------------------------------------------------------------------------
    Note:
    The default value of DB_FILES is operating system specific.

    --------------------------------------------------------------------------------




    With the Oracle Parallel Server, all instances must set the instance datafile upper bound to the same value.

    When determining a value for DB_FILES, take the following into consideration:

    If the value of DB_FILES is too low, you will be unable to add datafiles beyond the DB_FILES limit without first shutting down the database.

    If the value of DB_FILES is too high, memory is unnecessarily consumed.

    Theoretically, an Oracle database can have an unlimited number of datafiles. Nevertheless, you should consider the following when determining the number of datafiles:

    Performance is better with a small number of datafiles rather than a large number of small datafiles. A large number of files also increases the granularity of a recoverable unit.

    Operating systems often impose a limit on the number of files a process can open simultaneously. Oracle's DBWn processes can open all online datafiles. Oracle is also capable of treating open file descriptors as a cache, automatically closing files when the number of open file descriptors reaches the operating system-defined limit.

  3. #3
    Join Date
    Apr 2001
    Location
    London
    Posts
    725

    DETERMINE MAX DATAFILES.

    YOU CAN EITHER

    ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

    This will create a .trc file in udump.
    have a look at it for 'maxdatafiles' for that database.

    alternatively search V$controlfile_record_section.

    max datafiles in a tablespace is typically 1023 in 8i




    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

  4. #4
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    The max no# of datafiles is OS dependent but i 've read somewhere that on most operating systems it is 1022.
    And max no# of datafiles per db is 65533.
    You can have unlimited # of tablespaces, so if it's 65533 datafiles permitted. then you may not be able to exceed that number.

    Cheers!!!

    Tarry
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  5. #5
    Join Date
    Jan 2001
    Location
    Vancouver, Canada
    Posts
    87

    Max datafile allowed per tablespace?

    Thank you guys!!
    Mohammad Zahid
    Software Developer
    Database Management Applications.
    Vancouver, Canada
    E-mail: mzahid@shaw.ca

  6. #6
    Join Date
    Feb 2001
    Posts
    290
    Mazhid,
    As you are saying you have observing some performance issues , i think here you may wish to concentrate on I/O.

    Having 2000 tables in a single tablespace having multiple datafiles ( may be on just single disk , which i cant imagine ) which are not spread across multiple disks with separate disk controllers will be a performance HIT.

    Madhu Reddy
    xdollor@yahoo.com

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