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 data file or add the new data file 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 the above query. If this is a CDB database like 19c, we need to make sure we are in the correct pdb before executing the query
Alter session set container='TEST_PDB';
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') /
How to add datafile to tablespace in oracle
Add datafile to tablespace in oracle database is quite a simple task provided we check a few things before firing the add datafile command. A 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 stored in the filesystem. Specifically, we need to check the last name in the datafile. This is necessary to avoid adding the same datafiles names at two different locations. 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 at the full name of the datafile, we might add the datafile like /u101/app/oracle/testdata/example01.dbf. This will work well at present but might create future issues if you plan to consolidate all the datafiles in one filesystem, then one will overwrite another. So it is good to give a new datafile name such that the last name of the datafile is not same with existing one
(c) In case of CDB Database, we should be in correct PDB
Now that we have checked all the important things,we can add datafile to 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;
How to add Datafile to tablespace in case of RAC
All the above commands are valid in the case of Oracle RAC also. Just when are adding the datafiles, we need to make sure it is the shared filesystem. With ASM, we already know it is shared but with the filesystem sometimes this problem can occur
Troubleshooting
Generally, we should not have any issues with these commands. In case you added the file to the wrong location or wrong name, we can move the files to the correct location or name online using the below link. This is valid starting 12c
online move of active datafile
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