How to add datafile in tablespace in Oracle: Easy Guide

In Oracle Database, managing tablespace is crucial to maintain optimal performance and avoid downtime. When a tablespace is running low on space, you have two options: resizing the existing datafile or adding a new datafile. This blog post will guide you on how to add datafile in tablespace in Oracle. Let’s open VirtualBox and get started!

1. Check the Free Space in Tablespaces:

Before making any changes to a tablespace, you should first check how much free space is available. You can do this by running the following query:

set pages 46000 lines 32760
col tablespace_name format a35
col TABLESPACE_NAME heading "Tablespace|Name"
col Allocated_size heading "Allocated|Size(GB)" form 99999999.99
col Current_size heading "Current|Size(GB)" form 99999999.99
col Used_size heading "Used|Size(GB)" form 99999999.99
col Available_size heading "Available|Size(GB)" form 99999999.99
col Pct_used heading "%Used (vs)|(Allocated)" form 99999999.99
select x.tablespace_name
        ,x.alloc_size/1024/1024/1024 Allocated_size
        ,x.cur_size/1024/1024/1024 Current_Size
        ,(y.used+x.file_count*65536)/1024/1024/1024 Used_size
        ,(x.alloc_size-(y.used+x.file_count*65536))/1024/1024/1024 Available_size
        ,((y.used+x.file_count*65536)*100)/x.alloc_size Pct_used
from dba_tablespaces t
        ,(select t1.tablespace_name
        ,nvl(sum(s.bytes),0) used
        from dba_segments s
        ,dba_tablespaces t1
         where t1.tablespace_name=s.tablespace_name(+)
         group by t1.tablespace_name) y
        ,(select z.tablespace_name
        ,sum(greatest(z.bytes,nvl(z.maxbytes,0))) alloc_size
        ,sum(z.bytes) cur_size
        ,count(*) file_count
        from dba_data_files z
        group by z.tablespace_name) x
where t.tablespace_name=y.tablespace_name
and t.tablespace_name=x.tablespace_name
order by Pct_used desc
/

How to add datafile in tablespace in Oracle: Easy Guide

If you want to check any particular tablespace, then you can follow the below query:

set linesize 200
select x.tablespace_name
               ,sum(x.tot)/1024/1024 "Total Size(GB)"
               ,sum(x.sum)/1024/1024 "Total Free(GB)"
           ,sum(x.sum)*100/sum(x.tot) "% Free"
                    ,sum(x.tot-x.sum)*100/sum(x.tot) "% Used"
          ,sum(x.largest)/1024/1024 "Max Free(GB)"
           from
          (
            select tablespace_name
               ,0 tot
               ,sum(bytes) sum
              ,max(bytes) largest
        from dba_free_space x
      group by tablespace_name
      union
      select tablespace_name
            ,sum(bytes) tot
            ,0
            ,0
       from dba_data_files
      group by tablespace_name) x
        where x.tablespace_name ='&Tablespace_Name'
        group by x.tablespace_name
     ;

2. Check the existing data files in the particular tablespace:

set pages 1000
set lines 1000
col TABLESPACE_NAME format a20
col file_name format a30
COLUMN status FORMAT A15
COLUMN CURRENT_SIZE_GB FORMAT 999.99
COLUMN MAX_SIZE_GB FORMAT 999.99
SELECT x.TABLESPACE_NAME,y.FILE_NAME,y.STATUS,y.BYTES/1024/1024/1024 AS CURRENT_SIZE_GB,y.MAXBYTES/1024/1024/1024 AS MAX_SIZE_GB,y.AUTOEXTENSIBLE,x.BIGFILE
FROM DBA_TABLESPACES x JOIN DBA_DATA_FILES y ON x.TABLESPACE_NAME = y.TABLESPACE_NAME WHERE x.TABLESPACE_NAME = '&Tablespace_Name';

This query will provide a detailed breakdown of each data file status, current size, and max size. We can also check if auto-extension is enabled and if the data files are BIGFILE. A Bigfile Tablespace (BFT) is a type of tablespace that contains a single, very large datafile, potentially up to 128 terabytes. Please note that all the data files can be extended till MAX_SIZE_GB.So if the datafile can be extended then resize the datafile otherwise add a new datafile.

3. Resize a Datafile:

Before resizing, ensure that the filesystem or ASM disk group has sufficient space. Use the following commands to resize a datafile:

ALTER DATABASE DATAFILE '<file_path>' RESIZE 2G;

4. Add datafile in tablespace in Oracle:

First, check if OMF is Enabled. If DB_CREATE_FILE_DEST is set to a directory path, then OMF is enabled, and Oracle will use this directory for creating and managing the database files. So you do not need to specify the path while adding the new datafile.

Case 1: OMF is not enabled:

If resizing is not feasible or if you prefer to add additional space, you can add a new data file to the tablespace.

ALTER TABLESPACE <tablespace_name> ADD DATAFILE '<file_path>' SIZE <new_size>;

If you want the data file to increase in size when it’s full automatically, you can use the AUTOEXTEND clause:

ALTER TABLESPACE <tablespace_name> ADD DATAFILE '<file_path>' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 5G;

Here SIZE 100M means the current datafile size is 100M. AUTOEXTEND ON NEXT 10M means the datafile will automatically extend by 10 megabytes whenever it runs out of space and MAXSIZE 5G specifies that the datafile will not grow beyond 5 gigabytes.

Case 2: OMF is enabled:

If OMF is enabled then you can add a new data file to the tablespace by using the below query:

ALTER TABLESPACE <tablespace_name> ADD DATAFILE SIZE <new_size>;

If you want to use the AUTOEXTEND clause, then use the below:

ALTER TABLESPACE <tablespace_name> ADD DATAFILE SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 5G;

I hope you found this article helpful. Feel free to reach out if you have any questions to add datafile in tablespace in Oracle. If you need the DDL of a tablespace then you can follow my other article DDL of tablespace.