CREATING TABLESPACE

Creating and Increase the size of tablespace by adding datafiles.(AUTOEXTEND is disabled):

Normally ,we can the increase the tablespace size by adding the datafile to the tablesapce.

Create test tablespace with auntoextend is off,

SQL> create tablespace test datafile 'E:\APP\USER\ORADATA\DEV\test01.DBF' size 1
0M;

Tablespace created.

Auto extend is off:

SQL> select tablespace_name,autoextensible from dba_data_files;

TABLESPACE_NAME                AUT
------------------------------ ---
SYSTEM                         YES
SYSAUX                         YES
UNDOTBS1                       YES
USERS                          YES
TEST                           NO

Query the created tablespace :

SQL> select * from dba_data_files where tablespace_name ='TEST';

FILE_NAME
--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
E:\APP\USER\ORADATA\DEV\TEST01.DBF
        10 TEST                             10485760       1280 AVAILABLE
          10 NO           0          0            0    9437184        1152
ONLINE

Adding the datafile by increasing the tablespace,

SQL> alter tablespace test add datafile 'E:\APP\USER\ORADATA\DEV\TEST02.DBF' siz
e 20M;

Tablespace altered.


SQL> select * from dba_data_files where tablespace_name ='TEST';

FILE_NAME
--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
E:\APP\USER\ORADATA\DEV\TEST01.DBF
        10 TEST                             10485760       1280 AVAILABLE
          10 NO           0          0            0    9437184        1152
ONLINE


FILE_NAME
--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
E:\APP\USER\ORADATA\DEV\TEST02.DBF
        11 TEST                             20971520       2560 AVAILABLE
          11 NO           0          0            0   19922944        2432
ONLINE

Check the free size of the tablespace

SQL> select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
  2  from  (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
  3         from dba_free_space
  4         group by tablespace_name) a,
  5        (select tablespace_name, sum(bytes)/1024/1024 as tbs_size
  6         from dba_data_files
  7         group by tablespace_name) b
  8  where a.tablespace_name(+)=b.tablespace_name;

TABLESPACE_NAME                    SIZEMB     FREEMB
------------------------------ ---------- ----------
SYSAUX                                870      45.25
UNDOTBS1                              725     697.31
USERS                                   5       3.63
TEST                                   30         28
SYSTEM                                780        9.5

Adding the datafile to the tablespace.

SQL> alter tablespace test add datafile 'E:\APP\USER\ORADATA\DEV\TEST03.DBF' siz
e 40M;

Tablespace altered.

Now check the size after adding the datafile to tablespace:

SQL> select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
  2  from  (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space

  3         from dba_free_space
  4         group by tablespace_name) a,
  5        (select tablespace_name, sum(bytes)/1024/1024 as tbs_size
  6         from dba_data_files
  7         group by tablespace_name) b
  8  where a.tablespace_name(+)=b.tablespace_name;

TABLESPACE_NAME                    SIZEMB     FREEMB
------------------------------ ---------- ----------
SYSAUX                                870      45.25
UNDOTBS1                              725     697.31
USERS                                   5       3.63
TEST                                   70         67
SYSTEM                                780        9.5


SQL> select tablespace_name,autoextensible from dba_data_files;

TABLESPACE_NAME                AUT
------------------------------ ---
SYSTEM                         YES
SYSAUX                         YES
UNDOTBS1                       YES
USERS                          YES
TEST                           NO
TEST                           NO
TEST                           NO

7 rows selected.

Best Regards.