set line 180
set pages 1000
spool /users/oracle/pctused.log
col dummy noprint
col tablespace for a30       head 'Tablespace'
col rapport    for a27 head 'Utilise / Taille totale|(Mo)'
col alloc      for 999999 head 'Alloc|(Mo)'
col used       for 999999 head 'Used|(Mo)'
col free       for 999999 head 'Free|(Mo)'
col pctused    for a5        head 'Used|(%)'
col type       for a11        head 'Extent'
col ext        for 9,999 head 'Ext|(Mo)'
break on report
compute sum of alloc    on report
compute sum of free      on report
compute sum of used      on report

select to_char(nvl(used.Mo,0)  / (nvl(used.Mo,0) + nvl(free.Mo,0)) * 100,'999')||'%' pctused,
       to_char(nvl(used.Mo,0),'99999,999') || ' / ' ||
       to_char(alloc.Mo,'99999,999') || 'Mo' rapport,
       nvl(used.Mo,0)  / (nvl(used.Mo,0) + nvl(free.Mo,0)) * 100 dummy,
       decode(def.contents,'PERMANENT',decode(def.allocation_type,'USER','DICT','SYSTEM','LA','UNIFORM','LU' || ' - ' || round(MIN_EXTLEN / 1024 / 1024)|| 'Mo'),'UNDO','UNDO') type,
       def.tablespace_name tablespace
from
    dba_tablespaces def,
    ( select tablespace_name,
             sum(bytes) / 1024 / 1024 Mo
      from   dba_data_files
      group by tablespace_name ) alloc,
    ( select tablespace_name ,
             sum(bytes) / 1024 / 1024 Mo
      from   dba_segments
      group by tablespace_name ) used,
    ( select tablespace_name,
             sum(bytes) / 1024 / 1024 Mo
      from dba_free_space
      group by tablespace_name ) free
where def.contents != 'TEMPORARY'
and   def.tablespace_name = alloc.tablespace_name(+)
and   def.tablespace_name = used.tablespace_name(+)
and   def.tablespace_name = free.tablespace_name(+)
order by dummy;


spool off
