How to Check the Table space usage in Oracle database

 

An Oracle database consists of one or more logical storage units called TableSpaces, which collectively store all of the database’s data.

Each TableSpaces in an Oracle database consists of one or more files called datafiles, which are physical structures that conform to the operating system in which Oracle is running.

 

 

A database’s data is collectively stored in the datafiles that constitute each Tablespace of the database.

For example, the simplest Oracle database would have one Tablespace and one datafile. Another database can have three Tablespaces, each consisting of two datafiles (for a total of six datafiles).

 

Query to check table space utilisation[in MB]

 

Select
t.tablespace, t.totalspace as ” Totalspace(MB)”,
round((t.totalspace-fs.freespace),2) as “Used Space(MB)”,
fs.freespace as “Freespace(MB)”,
round(((t.totalspace-fs.freespace)/t.totalspace)*100,2) as “% Used”,
round((fs.freespace/t.totalspace)*100,2) as “% Free”
from
(select round(sum(d.bytes)/(1024*1024)) as totalspace, d.tablespace_name tablespace
from
dba_data_files d group by d.tablespace_name) t,
(select round(sum(f.bytes)/(1024*1024)) as freespace, f.tablespace_name tablespace
from
dba_free_space f
group by f.tablespace_name) fs
where t.tablespace=fs.tablespace
order by t.tablespace;

Above Query will give an output in below format. Which consist of

• Table space name
• Total Space available for particular Table Space
• Current utilisation
• Free space
• % of used and free space.

 

Query to check table space utilisation [Size and Free space only ] [GB]

 

select b.tablespace_name, tbs_size SizeGB, a.free_space FreeGB
from
(select tablespace_name, round(sum(bytes)/1024/1024/1024 ,2) as free_space
from dba_free_space
group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024/1024 as tbs_size
from dba_data_files
group by tablespace_name) b
where a.tablespace_name(+)=b.tablespace_name;

Above Query will give an output in below format. Which consist of

• Table space name
• Size of Table Space in GB
• Free Space in GB

 

Use below query get space in MB

select b.tablespace_name, tbs_size SizeMB, a.free_space FreeMB
from
(select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
from dba_free_space
group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as tbs_size
from dba_data_files
group by tablespace_name) b
where a.tablespace_name(+)=b.tablespace_name;