Display Used/Free space in Oracle Tablespaces

Introduction
To get tablespace used/free space information, we can use below script.

Column MBS_ALLOCATED represent total current size of the tablespace in Mbs.
Column MBS_FREE represent total free size of the tablespace in Mbs.
column MBS_USED represent total used size of the tablespace in Mbs.
column PCT_FREE represent total free size in precentage.
column PCT_USED represent total used size in precentage.
column MAX represent tablespace can be extend upto maximum size.
column USAGE_GRAPH display used size in 10 blocks.

column TABLESPACE_NAME format a20
column MBS_ALLOCATED format 9,999,999.99
column MBS_FREE format 9,999,999.99
column MBS_USED format 9,999,999.99
column PCT_FREE format 999.99
column PCT_USED format 999.99
column MAX format 9,999,999.99
column USAGE_GRAPH format a11

select x.*,
rpad('*',round((mbs_used*100/mbs_allocated)/10), '*') usage_graph
--trim(rpad(rpad(' ',round((mbs_used*100/mbs_allocated)/10)+1, chr(7)), 11, chr(8))) usage_graph
from (
-- for NON-TEMP tablespaces
select  a.tablespace_name,
  round(a.bytes_allocated / 1024 / 1024, 2) mbs_allocated,
  round(nvl(b.bytes_free, 0) / 1024 / 1024, 2) mbs_free,
  round((a.bytes_allocated - nvl(b.bytes_free, 0)) / 1024 / 1024, 2) mbs_used,
  round((nvl(b.bytes_free, 0) / a.bytes_allocated) * 100,2) Pct_Free,
  100 - round((nvl(b.bytes_free, 0) / a.bytes_allocated) * 100,2) Pct_used,
  round(maxbytes/1048576,2) Max
from  ( select  f.tablespace_name,
          sum(f.bytes) bytes_allocated,
          sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes
   from dba_data_files f
   group by tablespace_name) a,
 ( select  f.tablespace_name,
          sum(f.bytes)  bytes_free
   from dba_free_space f
   group by tablespace_name) b
where a.tablespace_name = b.tablespace_name (+)
union all
-- for TEMP tablespace
select h.tablespace_name,
  round(sum(h.bytes_free + h.bytes_used) / 1048576, 2) megs_allocated,
  round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / 1048576, 2) megs_free,
  round(sum(nvl(p.bytes_used, 0))/ 1048576, 2) megs_used,
  round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100,2) Pct_Free,
  100 - round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / sum(h.bytes_used + h.bytes_free)) * 100,2) pct_used,
  round(f.maxbytes / 1048576, 2) max
from   sys.v_$TEMP_SPACE_HEADER h, sys.v_$Temp_extent_pool p, dba_temp_files f
where  p.file_id(+) = h.file_id
and    p.tablespace_name(+) = h.tablespace_name
and    f.file_id = h.file_id
and    f.tablespace_name = h.tablespace_name
group by h.tablespace_name, f.maxbytes) x
ORDER BY tablespace_name;

TABLESPACE_NAME      MBS_ALLOCATED      MBS_FREE      MBS_USED PCT_FREE PCT_USED           MAX USAGE_GRAPH
-------------------- ------------- ------------- ------------- -------- -------- ------------- -----------
CWMLITE                      20.00         10.63          9.38    53.13    46.87     32,767.98 *****
DRSYS                        20.00         10.31          9.69    51.56    48.44     32,767.98 *****
EXAMPLE                     148.75           .13        148.63      .08    99.92     32,767.98 **********
INDX                         96.25          1.13         95.13     1.17    98.83     32,767.98 **********
OCC                          50.00         14.13         35.88    28.25    71.75         50.00 *******
ODM                          20.00         10.69          9.31    53.44    46.56     32,767.98 *****
SYSTEM                      490.00          5.19        484.81     1.06    98.94     32,767.98 **********
TEMP                         40.00         40.00           .00   100.00      .00     32,767.98
TOOLS                        10.00          3.94          6.06    39.38    60.62     32,767.98 ******
UNDOTBS1                    205.00        190.69         14.31    93.02     6.98     32,767.98 *
USERS                       142.50          1.06        141.44      .75    99.25     32,767.98 **********
XDB                          38.13           .19         37.94      .49    99.51     32,767.98 **********

12 rows selected.

SQL>