Possible Limitations When Adding Datafiles to a Tablespace

Before discussing limitations of adding datafiles to a tablespace let's think about DB_FILES parameter. DB_FILES is an initialization parameter in oracle. When an oracle instance starts, this 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. This parameter exists throughout the life cycle of the instance. So, changes to DB_FILES affect only after restarting the instance.

As this parameter indicates the amount of SGA space to reserve for datafile information the bigger value settings indicates memory is unnecessarily consumed.

And smaller value setting of this parameter impose limit to create another datafile.

So setting of DB_FILES parameter value is important.

Now let's look about possible limitations when adding datafiles to a tablespace.

Possible Limitations When Adding Datafiles to a Tablespace

•OS imposes a limit of the number of files a process can be opened at a time.

•OS imposes a limit of number and size of datafiles.

•The database imposes a maximum limit on the number of datafiles for any Oracle Database opened by any instance. This limit is operating system specific.

•Number of datafiles can't exceed DB_FILES initialization parameter.

•It is good to know that after oracle 8i the MAXDATAFILES parameter (of CREATE CONTROLFILE or CREATE DATABASE command) is not responsible of the limitations of adding datafiles.

•If you attempt to add a new file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, the MAXDATAFILES parameter of the control file will expand automatically to accommodate more files.

Related Documents
MAXDATAFILES , DB_FILES parameters and ORA-00059

How to Resize a Datafile

ORA-01667: cannot add any more tablespaces: limit of exceeded

New Feature of 10.2g: Eliminate Control File Re-Creation

When and How to Recreate the Controlfile