How to Extend BIGFILE Tablespace Datafile size in Oracle

How to Extend BIGFILE Tablespace Datafile size in Oracle                                                                

In This blog I am going to show how to extend BIGFILE Tablespace Datafile.

First of all need to check whether the tablespace is BIGFILE or not 

.For this issue below SQL statement.

1.       select tablespace_name,BIGFILE from dba_tablespaces ;

Example :-

TABLESPACE_NAME                 BIG

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

USERS                                            YES

SYSAUX                                          NO

From above result USERS Tablespace is BIGFILE Tablespace.

After this need to use below SQL statement for knowing the datafile associated with USERS Tablespace.

2.       select file_name ,tablespace_name, BYTES/1024/1024/1024 size_GB  from dba_data_files where tablespace_name='USERS';

FILE_NAME                                        TABLESPACE_NAME                       SIZE_GB

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

/Datafile/users01.dbf                                USERS                                                   2410

 

Issue below command to extend users tablespace datafile by only 1 GB for testing purpose only.

3.       Alter database datafile ‘/Datafile/users01.dbf’ resize 2411G;

Above query will extend the USERS tablespace datafile to 2411G.