ORA-32771: cannot add file to bigfile tablespace

Bigfile Tablespaces:

 A bigfile tablespace is a tablespace with only a single datafile which can grow upto terabytes based on Tablespace block size. Traditional small file tablespaces contain multiple datafiles, but the files cannot be as large.

Benefits of Bigfile Tablespaces:

• Bigfile tablespaces can significantly increase the storage capacity of an Oracle database. Smallfile tablespaces can contain up to 1024 files but bigfile tablespaces contain only one file that can be 1024 times larger than a smallfile tablespace. The total tablespace capacity is the same for smallfile tablespaces and bigfile tablespaces. However, because there is limit of 64K datafiles for each database, a database can contain 1024 times more bigfile tablespaces than smallfile tablespaces, so bigfile tablespaces increase the total database capacity by 3 orders of magnitude. In other words, 8 exabytes is the maximum size of the Oracle database when bigfile tablespaces are used with the maximum block size (32 k).

• Bigfile tablespaces simplify management of datafiles in ultra large databases by reducing the number of datafiles needed. You can also adjust parameters to reduce the SGA space required for datafile information and the size of the control file.

• They simplify database management by providing datafile transparency.

Considerations with Bigfile Tablespaces

• Bigfile tablespaces are intended to be used with Automatic Storage Management or other logical volume managers that support dynamically extensible logical volumes and striping or RAID.
• Avoid creating bigfile tablespaces on a system that does not support striping because of negative implications for parallel execution and RMAN backup parallelization.
• Avoid using bigfile tablespaces if there could possibly be no free space available on a disk group, and the only way to extend a tablespace is to add a new datafile on a different disk group.
• Using bigfile tablespaces on platforms that do not support large file sizes is not recommended and can limit tablespace capacity. Refer to your operating system specific documentation for information about maximum supported file sizes.
• Performance of database opens, checkpoints, and DBWR processes should improve if data is stored in bigfile tablespaces instead of traditional tablespaces. However, increasing the datafile size might increase time to restore a corrupted file or create a new datafile.

Creating a Bigfile Tablespace:

SQL> CREATE BIGFILE TABLESPACE BIGTBS DATAFILE '/u02/oracle/data/bigtbs01.dbf' SIZE 50G;

Add space to Bigfile tablespace:

SQL > ALTER TABLESPACE BIGTBS RESIZE 100G;

Common Errors:

SQL> ALTER TABLESPACE bigtbs

           ADD DATAFILE '/u02/oracle/data/bigtbs02.dbf' size 30G;

      ALTER TABLESPACE bigtbs

      *

      ERROR at line 1:

      ORA-32771: cannot add file to bigfile tablespace

SQL> ALTER DATABASE DATAFILE ‘/u02/oracle/data/bigtbs01.dbf' resize 100G;

       ERROR at line 1:

      ORA-XXXX: cannot resize data file for bigfile tablespace

Note: A Bigfile tablespace contain only onc datafile so you can only resize the existing datafile by mentioning tablespace name only ,not the datafile . Please find below the example.

SQL > ALTER TABLESPACE BIGTBS RESIZE 100G;

Identifying a Bigfile Tablespace:
   

DBA_TABLESPACES view provide information if a tablespace is Bigfile Tablespace.

SQL> select TABLESPACE_NAME, BIGFILE from DBA_TABLESPACES;

     TABLESPACE_NAME                BIGFILE

     -----------------------------        - ---------

     ...

     USERS                                SMALLFILE

     BIGTBS                                BIGFILE

References:
http://docs.oracle.com/cd/E11882_01/server.112/e25494/tspaces.htm#ADMIN13316