How to add datafile to tablespace in Oracle

Oracle tablespace stores the data in the Oracle database and as the data grows, we often need to add space to the tablespace. We can add space in two ways. we can either resize the existing datafile or add the new datafile to the tablespace.  We will check out adding datafile to tablespace in this post.

First we can check the total space,used space and free space in the tablespace using the below Sql

select a.tablespace_name, b.total,
c.available,
(b.total - c.available) used,
round((((b.total - c.available)*100)/b.total),2) pctused
from (select tablespace_name, (sum(bytes)/1024)/1024 total
from dba_data_files group by tablespace_name) b,
(select tablespace_name, nvl(round(((sum(bytes)/1024)/1024),2),0) available
from dba_free_space group by tablespace_name) c,
dba_tablespaces a
where a.tablespace_name = b.tablespace_name(+)
and a.tablespace_name = c.tablespace_name(+)
and a.tablespace_name = upper ('&1')
/

We can decide the space to be added depending on  the result of above query.

Add datafile to tablespace in oracle database is quite a simple task provided we check few things before firing the add datafile command. The few things are

a) Free space in the storage area.  We need to check the free space in the filesystem by using the command

df -h

Also if the storage area is oracle ASM, we need to check the free space in ASM storage using the command

asmcmd
lsdg

If sufficient free space is present in the storage area,then we are good on free space

b) We need to check the names of the existing datafiles in the tablespace in case the files are storage in filesystem. Specifically we need to check the last name in the datafile. This is  necessary to avoid adding same datafiles names at two different location. Check the last name in the existing datafile and add the datafile which is not existing

We can check the existing datafile using the below query

select file_name,bytes/1024/1024 from dba_data_files where tablespace_name like '&1';

FileName Bytes/1024/1024
---------------------------------------- ----------------
/u100/app/oracle/testdata/example01.dbf 1000
/u100/app/oracle/testdata/example02.dbf 1000
/u100/app/oracle/testdata/example03.dbf 1000
/u101/app/oracle/testdata/example04.dbf 1000
/u101/app/oracle/testdata/example05.dbf 1000
/u101/app/oracle/testdata/example06.dbf 1000

Here we should check the last name of the datafiles i.e example01.dbf ,example02.dbf  etc.  if we need to add ,we should be adding example07.dbf

If we just look the full name of the datafile, we might add the datafile like /u101/app/oracle/testdata/example01.dbf .  This will work good at present but it might create issues in future if you plan to consolidate all the datafiles in one filesystem, then one will overwrite other. So it is good to give new datafile name such that last name of the datafile is not same with existing one

Now that we have check all the important things ,we can add datafile in tablespace

We  can use ALTER TABLESPACE to add datafile in tablespace like

Default Tablespace  ( Normal tablespace ,Undo tablespace)

Syntax

ALTER TABLESPACE <tablespace_name> ADD DATAFILE <location_of_datafile>

Example

ALTER TABLESPACE TEST ADD DATAFILE '/u101/app/oracle/testdata/example07.dbf' SIZE 1000M

With Oracle ASM Diskgroups.

We can just add just specifying the data diskgroup name

ALTER TABLESPACE TEST ADD DATAFILE '+DATA ' SIZE 1000M;

Alter tablespace to add multiple datafiles

SQL>alter tablespace tablespace_name add datafile
<’destination 1’> size 10M autoextend on next 124M,
<’destination 2’> size 10M autoextend on next 124M,
<’destination 3’> size 10M autoextend on next 124M;

TEMP table space

Syntax

ALTER TABLESPACE <tablespace_name> ADD TEMPFILE ‘<location_of_tempfile>’ SIZE <size>

Example

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oracle/TEST/oradata/temp_4.dbf' SIZE 1000M;

With ASM

ALTER TABLESPACE TEST ADD TEMPFILE '+DATA ' SIZE 1000M;

Hope you like this post on How to add datafile to tablespace in Oracle.

Related Articles

How to check Tablespace in Oracle :Learn about How to Check Tablespace in Oracle, tablespace free space,tablespace growth information,tablespace size ,associated datafiles ,tablespace size
shrink datafile in Oracle : Check out how to shrink the datafile and reclaim space on the filesystem. How to resolve ORA-03297
ORA-01652: ORA-01652 error usually because when the tablespace does not have free space in Permanent and Temporary tablespace in oracle database.Check out how to resolve it
Linux command for Oracle DBA
how to change default tablespace in oracle :Default tablespace are the tablespace where the objects are created when no tablespace name is specified by users.Find out how to check default tablespace
How to check temp tablespace in Oracle :This article is about temp tablespace in Oracle, resize the tempfile, drop the tempfile,find the temp usage by Session