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.
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.
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.
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.
I'm a JOLE(JavaOracleLinuxEnthusiast)
--- Everything was meant to be---
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.