How to Show Used/Free Tablespace Space in Oracle DB

This script will list tablespace wise free space and used space (also total size) as well as total space. For running this query you must have SELECT privileges to SYS.DBA_FREE_SPACE, SYS.V_$DATAFILE, SYS.V_$TABLESPACE views.


SELECT Total.name "Tablespace Name",
       nvl(Free_space, 0) "Free Size(MB)",
       nvl(total_space-Free_space, 0) "Used Size(MB)", 
       total_space "Total Size(MB)"
FROM
  (select tablespace_name, sum(bytes/1024/1024) free_space
     from sys.dba_free_space
    group by tablespace_name
  ) Free,
  (select b.name,  sum(bytes/1024/1024) total_space
     from sys.v_$datafile a, sys.v_$tablespace B
    where a.ts# = b.ts#
    group by b.name
  ) Total
WHERE Free.Tablespace_name(+) = Total.name
ORDER BY Total.name;