Oracle: List freespace by Tablespaces

Use the following script to get sizing (free,used etc) for tablespaces:


------------------------------------------------------------------------------
-- This SQL Plus script lists freespace by tablespace
------------------------------------------------------------------------------
set linesize    200
set pages       100
set verify off
set feedback off


col stat                        for a4          trunc   head Stat
col content                     for a4          trunc   head Type
col tablespace_name             for a25                 head Tablespace
col MB                          for 9,999,999              head "Tot|(MB)"
col free                        for 9,999,999              head "Free|(MB)"
col largest                     for 99,999,999           head "Largest   |Extent (K)"
col percent                     for a7                  head "% Free"
col extent_management           for a4          trunc   head "Ext.|Mng"
col allocation_type             for a4          trunc   head "Allc|type"
col segment_space_management    for a6          trunc   head "Space|Mng"
col pct_increase                for 99                  head "Pct |Inc."


break on report
compute sum of "free"  on report
compute sum of "MB" on report


-- 100 - (NVL (t.bytes / a.bytes * 100, 0)) percent,




SELECT substr(d.status,0,3) stat , substr(d.contents,0,1) content,
       d.tablespace_name,
       NVL (a.bytes / 1024 / 1024, 0) MB,
       NVL (f.bytes / 1024 / 1024, 0) free,
       NVL (f.large / 1024, 0) largest,
       '  '||round(f.bytes/a.bytes*100,0)||'%' percent,
       d.extent_management, d.allocation_type,
       d.pct_increase, d.segment_space_management
  FROM sys.dba_tablespaces d,
       (SELECT   tablespace_name, SUM(bytes) bytes
            FROM dba_data_files
        GROUP BY tablespace_name) a,
       (SELECT   tablespace_name, SUM(bytes) bytes, MAX(bytes) large
            FROM dba_free_space
        GROUP BY tablespace_name) f
 WHERE d.tablespace_name = a.tablespace_name(+)
   AND d.tablespace_name = f.tablespace_name(+)
   AND NOT (    d.extent_management LIKE 'LOCAL'
            AND d.contents LIKE 'TEMPORARY'
           )
UNION ALL
SELECT substr(d.status,0,3) stat, substr(d.contents,0,1) content,
       d.tablespace_name,
       NVL (a.bytes / 1024 / 1024, 0) MB,
       NVL (a.bytes - NVL (t.bytes, 0), 0) / 1024 / 1024 free,
       NVL (t.large / 1024, 0) largest,
       '  '||round((1-NVL(t.bytes,0)/a.bytes)*100,0)||'%' percent,
       d.extent_management, d.allocation_type,
       d.pct_increase, d.segment_space_management
  FROM sys.dba_tablespaces d,
       (SELECT   tablespace_name, SUM(bytes) bytes
            FROM dba_temp_files
        GROUP BY tablespace_name) a,
       (SELECT   tablespace_name, SUM(bytes_cached) bytes,MAX(bytes_cached) large
            FROM v$temp_extent_pool
        GROUP BY tablespace_name) t
 WHERE d.tablespace_name = a.tablespace_name(+)
   AND d.tablespace_name = t.tablespace_name(+)
   AND d.extent_management LIKE 'LOCAL'
   AND d.contents LIKE 'TEMPORARY'
/