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;
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;
yes
Great post made here. One thing I’d like to say is that most professional job areas consider the Bachelors Degree as the entry level requirement for an online degree. Whilst Associate College diplomas are a great way to begin, completing your Bachelors opens many entrance doors to various employment goodies, there are numerous on-line Bachelor Course Programs available from institutions like The University of Phoenix, Intercontinental University Online and Kaplan. Another thing is that many brick and mortar institutions offer Online editions of their diplomas but usually for a substantially higher payment than the companies that specialize in online college diploma programs.
The articles you write help me a lot and I like the topic