Tablespace Utilisation Query

 The below query gives a list of the tablespaces, total size, free size in MB, used size in MB and  tablespace  percentage full. This is a very useful query whenever I need  to make any changes to the tablespaces add or decrease the size of existing datafiles.

SQL> select a.tablespace_name, assigned_space "ASSIGNED (MB)", (nvl(free_space,0) + INCRM) "FREE (MB)", (assigned_space - nvl(free_space,0) - INCRM) "USED (MB)",
  ((assigned_space - nvl(free_space,0) - INCRM) / assigned_space)*100 "% Full"
from
(select  tablespace_name, count(*) num1, sum(DECODE(SIGN(maxbytes - bytes), -1, bytes, maxbytes))/(1024*1024) assigned_space, sum(DECODE(SIGN(maxbytes - bytes), -1, bytes, maxbytes))/(1024*1024) - sum(bytes)/(1024*1024) INCRM
from dba_data_files group by tablespace_name) A,
(select  tablespace_name, count(*) num2, (sum(bytes)/(1024*1024)) free_space
from dba_free_space a group by tablespace_name) B
where b.tablespace_name(+)=a.tablespace_name
union all select a.tablespace_name, assigned_space "ASSIGNED (MB)", (nvl(free_space,0) + INCRM) "FREE (MB)", (assigned_space - nvl(free_space,0) - INCRM) "USED (MB)",
((assigned_space - nvl(free_space,0) - INCRM ) / assigned_space)*100 "% Full"
from
(select  tablespace_name, count(*) num1, sum(DECODE(SIGN(maxbytes - bytes), -1, bytes, maxbytes))/(1024*1024) assigned_space,
sum(DECODE(SIGN(maxbytes - bytes), -1, bytes, maxbytes))/(1024*1024) - sum(bytes)/(1024*1024) INCRM from dba_temp_files group by tablespace_name) A,
(select  tablespace_name, count(*) num2, (sum(bytes)/(1024*1024)) free_space
from dba_free_space group by tablespace_name) B
where b.tablespace_name(+)=a.tablespace_name;