Data File

Restrictions on Adding a Datafile

There are two major restrictions must be considered before you add a datafile to a tablespace. One is about the number limit of datafiles, the other is about the size limit of a datafile.

Allowable Number of Datafiles

DB_FILES in spfile

You can regards the initialization parameter as a soft limit, if you add a datafile and hit ORA-00059, you can increase the value to fit your requirement. To check current number limit of datafiles in spfile, please issue the following:

SQL> show parameter db_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------
db_files                             integer     200

MAXDATAFILES in controlfile

This parameter resides in the controlfile. Before 8i, the parameter is a hard limit relative to DB_FILES, if an attempt to add a datafiles exceeding the limit, it will fail. Increasing DB_FILES won’t save you a thing, you must recreate a new controlfile with larger value of MAXDATAFILES.

But since 8i , if an attempt to add a datafile under DB_FILES limit, MAXDATAFILES will increase the value automatically to accommodate and manage more datafiles. To check current number limit of datafiles in controlfile, please issue the following:

SQL> SELECT records_total FROM v$controlfile_record_section WHERE type = 'DATAFILE';


Operating System Restrictions

If a database adopts file system rather than raw devices or ASM as a storage, you need to think over OS limit. But I think most of databases never hit the limit all their lifecycle.

The size limit of a datafile

Database limitation

Since Oracle can manage a small file no more than 4194302 (2^22 – 2) blocks, so the maximum size of a datafile in a small file tablespace depends on db_block_size. You may refer to the post: How Big a Data File Can Be.

Operation system limitation.

On older Unix like Solaris 8, the maximum file size is 2GB, if you are maintaining 8i or 9i database that is running on those plateform, you should be aware of the limitation.

Leave a Reply

Your email address will not be published. Required fields are marked *