How to add datafile to tablespace in Oracle

Last updated on March 22nd, 2019 at 04:10 am

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 ASM.

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

Tablespace Name ,Size ,Free space,datafiles ,growth in Oracle Database

What you need to know about create tablespace statement in Oracle

How to shrink the datafile in Oracle

ORA-01652: unable to extend temp segment: Solutions

29 Useful Unix/Linux command for Oracle DBA

Leave a Reply