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
Also if the storage area is 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