Check Free Space In Tablespaces

The following SQL helps to check the tablespace sizes and the free space available. We can even see the largest continuous space available.

select *
from   (
 select tsp.tablespace_name
 ,      round(df.sum_bytes/1024/1024) total_mb
 ,      round(df.sum_bytes/1024/1024 - nvl(fs.sum_free,0)/1024/1024) sum_used_mb
 ,      round(nvl(fs.sum_free,0)/1024/1024) sum_free_mb
 ,      round(df.sum_bytes/1024/1024/1024) total_gb
 ,      round(df.sum_bytes/1024/1024/1024 - nvl(fs.sum_free,0)/1024/1024/1024) sum_used_gb
 ,      round(nvl(fs.sum_free,0)/1024/1024/1024) sum_free_gb
 ,      round(nvl(fs.sum_free/df.sum_bytes *100,0)) pct_free
 ,      substr(tsp.status,1,2)||substr(contents,1,1) status
 ,      tsp.extent_management
 ,      tsp.allocation_type
 ,      case
            when 'YES' in
              (select autoextensible
               from   dba_data_files df
               where  df.tablespace_name = tsp.tablespace_name
              )
            then 'Y'
            else 'N'
        end as auto
 ,      tsp.logging
 ,      round(nvl(fs.max_bytes,0)/1024/1024) max_free_mb
 ,      nvl(fs.cnt_free,0) free_count
 from   dba_tablespaces tsp
 ,      (
           select tablespace_name
           ,      sum(bytes) sum_free
           ,      max(bytes) max_bytes
           ,      count(*) cnt_free
           from   dba_free_space
           group by tablespace_name
        ) fs
      , (
           select tablespace_name
           ,      sum(bytes) sum_bytes
           from   dba_data_files
           group by tablespace_name
        ) df
 where  tsp.tablespace_name = fs.tablespace_name(+)
 and    tsp.tablespace_name = df.tablespace_name
 union all
 select tsp.tablespace_name
 ,      round(df.sum_bytes/1024/1024) total_mb
 ,      round(df.sum_bytes/1024/1024 - (df.sum_bytes-nvl(fs.sum_free,0))/1024/1024) sum_used_mb
 ,      round((df.sum_bytes-nvl(fs.sum_free,0))/1024/1024) sum_free_mb
 ,      round(df.sum_bytes/1024/1024/1024) total_gb
 ,      round(df.sum_bytes/1024/1024/1024 - (df.sum_bytes-nvl(fs.sum_free,0))/1024/1024/1024) sum_used_gb
 ,      round((df.sum_bytes-nvl(fs.sum_free,0))/1024/1024/1024) sum_free_gb
 ,      round(((df.sum_bytes-nvl(fs.sum_free,0))/df.sum_bytes*100)) pct_free
 ,      substr(tsp.status,1,2)||substr(contents,1,1) status
 ,      tsp.extent_management
 ,      tsp.allocation_type
 ,      case
            when 'YES' in
              (select autoextensible
               from   dba_temp_files tf
               where  tf.tablespace_name = tsp.tablespace_name
              )
            then 'Y'
            else 'N'
        end as auto
 ,      tsp.logging
 ,      null max_free_mb
 ,      null count
 from   dba_tablespaces tsp
 ,      (
          select tablespace_name
          ,      sum(bytes_cached) sum_free
          from   v$temp_extent_pool
          group by tablespace_name
        ) fs
 ,      (
          select tablespace_name
          ,      sum(bytes) sum_bytes
          from   dba_temp_files
          group by tablespace_name
        ) df
 where  tsp.tablespace_name = fs.tablespace_name(+)
 and    tsp.tablespace_name = df.tablespace_name
)
order by tablespace_name
;