Listing A

col "Tablespace" for a13

col "Used MB"    for 99,999,999

col "Free MB"    for 99,999,999

col "Total MB"   for 99,999,999

col "Block Size" for 9,999,999

 
select

   df.tablespace_name                          "Tablespace",

   block_size                                  "Block Size",

   (df.totalspace - fs.freespace)              "Used MB",

   fs.freespace                               "Free MB",

   df.totalspace                               "Total MB",

   round(100 * (fs.freespace / df.totalspace)) "Pct. Free"

from

   dba_tablespaces                               ts,

   (select tablespace_name,

        round(sum(bytes) / 1048576) TotalSpace

      from dba_data_files

      group by tablespace_name)                  df,

   (select tablespace_name,

        round(sum(bytes) / 1048576) FreeSpace

      from dba_free_space

      group by tablespace_name)                 fs

where

   ts.tablespace_name = fs.tablespace_name

and

   df.tablespace_name = fs.tablespace_name(+)

;