This can have a negative performance impact. Oracle is 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. Oracle's DBW n processes can open all online datafiles. Oracle allows more datafiles in the database than the operating system defined limit. The number of datafiles comprising a tablespace, and ultimately the database, can have an impact upon performance. 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.
Operating systems impose limits on the number and size of datafiles.More datafiles cannot be created when the operating system limit of open files is reached. Operating systems often impose a limit on the number of files a process can open simultaneously.You can add datafiles to tablespaces, subject to the following limitations: Limitations When Adding Datafiles to a Tablespace If the value of DB_FILES is too high, memory is unnecessarily consumed.If the value of DB_FILES is too low, you cannot add datafiles beyond the DB_FILES limit without first shutting down the database.When determining a value for DB_FILES, take the following into consideration: The default value of DB_FILES is operating system specific. You can change the value of DB_FILES (by changing the initialization parameter setting), but the new value does not take effect until you shut down and restart the instance. This limit applies for the life of the instance.
When starting an Oracle instance, the DB_FILES initialization parameter indicates the amount of SGA space to reserve for datafile information and thus, the maximum number of datafiles that can be created for the instance. Determine the Value of the DB_FILES Initialization Parameter The following are some guidelines to consider when determining the number of datafiles for your database. A small system might have a single datafile. Store Datafiles Separate from Redo Log FilesĪt least one datafile is required for the SYSTEM tablespace of a database.This section describes aspects of managing datafiles, and contains the following topics: This file number is also displayed in the FILE_ID column of the DBA_DATA_FILES or DBA_TEMP_FILES view. When using a file number, specify the file number that is displayed in the FILE# column of the V$DATAFILE or V$TEMPFILE view. You can optionally use file numbers instead of file names to identify datafiles or tempfiles in SQL statements. However, when the number of datafiles in a database exceeds a threshold (typically 1023), the relative file number differs from the absolute file number.įile numbers are displayed in many data dictionary views. For small and medium size databases, relative file numbers usually have the same value as the absolute file number. Uniquely identifies a datafile within a tablespace. In earlier releases of Oracle, the absolute file number may have been referred to as simply, the "file number." Uniquely identifies a datafile in the database. These numbers are described in the following table: Type of File Number Oracle assigns each datafile two associated file numbers, an absolute file number and a relative file number, that are used to uniquely identify it. They must be explicitly created for each tablespace. Chapter 3, "Using Oracle-Managed Files" for information about creating datafiles and tempfiles that are both created and managed by the Oracle database serverĭatafiles are physical files of the operating system that store the data of all logical structures in the database.