Create tablespace in oracle database.

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

Create tablespace

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’;

How to create tablespace?

Create Single datafile.

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

Create multiple datafiles.

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

Note:- replace the create tabxespace command as previous.

Leave a Comment