ORA-32771: Cannot Add File To Bigfile Tablespace

When trying to add a datafile to a tablespace , got the error – ORA-32771: cannot add file to bigfile tablespace.


SQL> ALTER TABLESPACE BIG_TBSP1 add datafile ‘+DATA/df01.dbf’ size 130G;
ALTER TABLESPACE BIG_TBSP1 add datafile ‘+DATA/df01.dbf’ size 130G;
*
ERROR at line 1:
ORA-32771: cannot add file to bigfile tablespace


SOLUTION:


A bigfile tablespace can contain only one datafile and no other datafile can be added to that.


SQL> select BIGFILE from dba_tablespaces where tablespace_name=’BIG_TBSP1’;


BIGFILE
———————– —
YES


How to increase space in Bigfile Tablespace?




BIGFILE tablespace stores its data in a single datafile with a much larger capacity.


We can resize the size of the datafile in BIGFILE tablespace using ALTER DATABASE Command




ALTER DATABASE DATAFILE ‘/+DATA/df0101.dbf’ RESIZE 180G;


Since BIGFILE Tablespace has only one datafile, there is no need to identify the datafile and increase its size.
We can use ALTER TABLESPACE command to resize at the tablespace level.


ALTER TABLESPACE BIG_TBSP1 RESIZE 180G;

One thought on “ORA-32771: Cannot Add File To Bigfile Tablespace”

Leave a Reply

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