Query to see the size of all Oracle TABLESPACE

Published by Edoardo on 4 December 20204 December 2020

With the article “Query to see the size of all Oracle TABLESPACE” it will be possible to identify all the dimensions relating to the TABLESPACE. The selected fields are:

TABLESPACE_NAME – Name of the tablespaces
TOTAL_AUTO_MB – Total space of the Tablespace in MB
TOTAL_NOW_MB – Total allocated space of the tablespace in MB
USED_MB – Used space of the tablespace in MB
FREE_MB – Already allocated free space of the tablespace in MB
POS._FREE_MB – Free space in MB that can still be used by the AUTOEXTEND
% USED – Percentage of used space
% POSSIB_USED – Percentage of space that can still be used by the AUTOEXTEND

The information is retrieved from the DBA_FREE_SPACE, DBA_DATA_FILES tables.

Finally the tables are related through the JOIN <br> a.tablespace_name = b.tablespace_name (+).

Query to see the size of all Oracle TABLESPACE:

set linesize 200
col tablespace_name for a25
col "Total Auto Mb"  for 999,999,999,999
col "Total Now Mb" for 999,999,999,999
col "Used Mb"   for 999,999,999,999
col "Free Mb"  for 999,999,999,999
col "Pos. Free Mb" for 999,999,999,999
col "Total Auto Mb"
col "% Used" for a10
col "% Possib. Used" for a10
col  dummy noprint
break on dummy
compute sum of "Total Mb" on dummy
compute sum of "Used Mb"  on dummy
compute sum of "Free Mb" on dummy
select null dummy ,
a.tablespace_name ,
a.total_possible as "Total Auto Mb" ,
a.total as "Total Now Mb",
a.total-nvl(b.free,0) as "Used Mb"
,nvl(b.free,0) as "Free Mb",
a.total_possible-(a.total-nvl(b.free,0)) as "Pos. Free Mb",
trunc(((a.total-nvl(b.free,0))/a.total)*100)||'%' as "% Used",
trunc(((a.total-nvl(b.free,0))/a.total_possible)*100)||'%' as "% Possib. Used"
from (select tablespace_name,trunc(sum(bytes)/1024/1024) free
from dba_free_space group by tablespace_name) b,
(select tablespace_name,trunc(sum(greatest(bytes,maxbytes))/1024/1024) total_possible ,
trunc(sum(bytes)/1024/1024) total
from dba_data_files group by tablespace_name) a
where a.tablespace_name = b.tablespace_name (+)
--and b.tablespace_name='USERS'
order by 2
/

Furthermore, the article describes the tables from which the information is retrieved and how they are related through the JOIN statement, making it easy to understand the logic behind the query.

Overall, this article is an excellent resource for Oracle DBAs and database developers who want to efficiently monitor the TABLESPACE of their Oracle database.

To view my guides on CRS Commands, click here!
Instead, you will find SQL queries at this LINK.

Stay up to date on our activities by joining our community:
Facebook
Youtube
Linkedin

If you have any questions, do not hesitate to comment on the article.