Create tablespace in oracle database.

If you want to increase the size of oracle database. We must create tablespace or increase the size of existing tablespace by resize datafile or add datafile in existing tablespace.

Create Tablespace example.

Check Tablespace size in oracle database.

SELECT /* + RULE */  df.tablespace_name "Tablespace",
       df.bytes / (1024 * 1024) "Size (MB)",
       SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs,
       (SELECT tablespace_name,SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,df.bytes
 order by 5 desc;

Check datafile size:-

select file_name,bytes/1024/1024 MB from dba_data_files where tablespace_name='&TablespaceName';

Create tablespace with Single datafile.

SQL>Create tablespace xxcustom datafile ‘/path/prod/xxcustom01.dbf’ size 300m;

Create tablespace with multiple datafiles.

SQL>Create tablespace xxcustom 
           datafile   ‘/path/prod/xxcustom01.dbf’ size 300m, 
                          ‘/path/prod/xxcustom02.dbf’ size 300m,
                          ‘/path/prod/xxcustom03.dbf’ size 300m;