How to Add Datafile to Tablespace in Oracle

December 16, 2018

Datafiles can be located on ASM or file system on disk. So either you can add datafile in ASM or else you can add datafiles to tablespace in Oracle located on file system.

Before adding datafile in Oracle you can check its initial allocated size which is bytes and how much it can grow or extend if autoextend is on which is referred as maxbytes. You can also check free tablespace size so that you will get an overall idea on how much space you should add to increase tablespace size.

ORA-01653: unable to extend table xyz by SOME_SIZE in tablespace TABLESPACE_NAME is very common error related to tablespace. This is because data has reached to the high watermark and now datafiles are unable to extend further more. So in this case you need to add or extend the datafiles.

Caching a Table in Keep Cache Buffer Pool for faster access

Using below query you can check the existing tablespace size, add datafiles, increasing autoextends and resizing the datafiles.

How to Add/Resize datafiles in Oracle?

Query to Check Tablespace Size 
set line 1000;
col file_id for a6;
col file_name for a55;
col TABLESPACE_NAME for a30;
set pagesize 128;
set feedback off;
select  a.TABLESPACE_NAME,
       a.BYTES/1024/1024/1024 GB_used,
       b.BYTES/1024/1024/1024 GB_free,
       b.largest,
       round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
from
       (
               select  TABLESPACE_NAME,
                       sum(BYTES) BYTES
               from    dba_data_files
               group   by TABLESPACE_NAME
       )
       a,
       (
               select  TABLESPACE_NAME,
                       sum(BYTES) BYTES ,
                       max(BYTES) largest
               from    dba_free_space
               group   by TABLESPACE_NAME
       )
       b
where   a.TABLESPACE_NAME=b.TABLESPACE_NAME
order   by ((a.BYTES-b.BYTES)/a.BYTES) desc;

Steps to Add Datafile in Oracle ASM

Before adding datafiles, please check the location of the datafiles so that you can use the same location for new datafile. If datafiles are in ASM, no need to provide .dbf extension. Only diskgroup name is sufficient to add datafiles. If datafiles are located on filesystem then provide the full location and name of the datafile with .dbf extension.

Query to Check File_name, initial size and maxsize:
select FILE_NAME,FILE_ID,AUTOEXTENSIBLE,bytes/1024/1024/1024,STATUS,MAXBYTES/1024/1024/1024 from dba_data_files where TABLESPACE_NAME='&TABLESPACE_NAME';
To check the location of existing datafiles: 
show parameter db_create_file_dest;
select file_name from dba_data_files;
Query to Add Datafile to the Tablespace: 
ALTER TABLESPACE TABLESPACE_NAME ADD DATAFILE '+DATA' SIZE 16M AUTOEXTEND ON MAXSIZE 31G;
To enable datafile autoextend and set the Maxsize 
SQL> ALTER DATABASE datafile 'Datafile_name' autoextend ON maxsize 120 G;
To Resize Datafiles 
SQL> alter database datafile 'datafile_name' resize 20G;

Must Read: