Tablespace

Tablespace
-------------

set pagesize 300
set linesize 100
column tablespace_name format a30 heading 'Tablespace'
 column sumb format 999,999,999
    column extents format 9999
    column bytes format 999,999,999,999
    column largest format 999,999,999,999
    column Tot_Size format 999,999 Heading 'Total Size(GB)'
    column Tot_Free  format 999999.99 Heading  'Total Free(Mb)'
    column Pct_Free format 999.99 heading '% Free'
         column Max_Free format 999,999,999 heading 'Max Free(Kb)'
ttitle center 'Tablespaces With Less Than 10% Free Space' skip 2
select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
    sum(a.sumb/1024/1024) Tot_Free,
    sum(a.sumb)*100/sum(a.tots) Pct_Free
        from
    (
    select tablespace_name,0 tots,sum(bytes) sumb
      from dba_free_space a
    group by tablespace_name
    union
     select tablespace_name,sum(bytes) tots,0 from
      dba_data_files
     group by tablespace_name) a
     group by a.tablespace_name
having  sum(a.sumb)*100/sum(a.tots) < 10
order by pct_free;