Tablespace size of an Oracle Database

How can I know the size of my Oracle Database? What is the size of the Tablespaces in an Oracle Database?
An Oracle database stores the data in logical units called Tablespaces. Therefore, to know the size of the whole instance you need to know the size of each tablespace.

Tablespace size query

With this query you can check the free space, total size and usage percentage of each tablespace.

select
tablespace,
Free,
Total,
TRUNC((100-((Free/total)*100)),2) USED_PCT
from
(
select tablespaces.tablespace_name tablespace,ROUND(nvl(sum(fr.BYTES/1024/1024),0)) Free
from dba_free_space fr
right outer join dba_tablespaces tablespaces
on tablespaces.tablespace_name=fr.tablespace_name
group by tablespaces.tablespace_name
order by 2 desc nulls last)
fre,
(select tablespace_name, sum(bytes)/1024/1024 total from dba_data_files group by tablespace_name)
siz
where
fre.tablespace=siz.tablespace_name order by PCT_USADO desc;

 

Leave a Reply

Your email address will not be published.