Restrictions on Adding a Datafile

  • by

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';

RECORDS_TOTAL
-------------
         1024

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 life cycle.

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 2 GB, if you are maintaining 8i or 9i database that is running on those platform, you should be aware of the limitation.

Leave a Reply

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