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
Leave a Reply