ORA-32771 and ORA-32772

Bigfile tablespaces were introduced, and this example was run, in Oracle 10. They have only one datafile. Usually, this will be very large, although this is not the case in the example below:

 

SQL> create bigfile tablespace andrew

  2  datafile '/tmp/andrew.dbf' size 10m

  3  /

 

Tablespace created.

 

SQL>

 

There are two ways to extend an ordinary tablespace. You can add an extra datafile or you can extend an existing datafile. You cannot add an extra datafile to a bigfile tablespace so, to avoid running out of space altogether, you should use them with file systems which can be dynamically extended. These can be created, for example, on an EMC Celerra. You can identify these tablespaces as follows:

 

SQL> l

  1  select bigfile from dba_tablespaces

  2* where tablespace_name = 'ANDREW'

SQL> /

 

BIGFILE

-------

YES

 

SQL>

 

If you try to add an extra file to a bigfile tablespace, you get the following error:

 

SQL> alter tablespace andrew

  2  add datafile '/tmp/one_file_too_many.dbf'

  3  size 10m

  4  /

alter tablespace andrew

*

ERROR at line 1:

ORA-32771: cannot add file to bigfile tablespace

 

SQL>

 

Bigfile tablespaces must be locally managed, they cannot be dictionary managed so, in the example below, you would replace extent management dictionary with extent management local:

 

SQL> l

  1  create bigfile tablespace fred

  2  extent management dictionary

  3* datafile '/tmp/fred.dbf' size 5m

SQL> /

create bigfile tablespace fred

*

ERROR at line 1:

ORA-32772: BIGFILE is invalid option for this type of

tablespace

 

SQL>

More...