Oracle : Etat des tablespaces

                               Size                   Used          Free
Tablespace                     (Mb)                  (Mb)          (Mb)  Used %   Free %
------------------------------ ------------- ------------- ------------- -------- --------
PSAPSR3                           165,000.00    154,027.63     10,966.38 93.35%   6.65%
PSAPSR3700                         46,000.00     36,642.13      9,355.88 79.66%   20.34%
PSAPSR3FACT                           100.00           .00         99.00 0%       99%
PSAPSR3ODS                            100.00           .00         99.00 0%       99%
PSAPSR3USR                          1,000.00         62.69        936.31 6.27%    93.63%
PSAPTEMP                           20,000.00      5,823.00     14,177.00 29.12%   70.89%
PSAPUNDO                           26,800.00         32.00     26,767.00 .12%     99.88%
SYSAUX                              7,220.00      6,706.00        513.00 92.88%   7.11%
SYSTEM                              1,180.00      1,172.31          6.69 99.35%   .57%
                               ------------- ------------- -------------
Totals:                           267,400.00    204,465.76     62,920.26

9 rows selected.

set pagesize 50
set linesize 120
set heading on
column tablespace_name heading 'Tablespace' justify left format a30 truncated
column tbsize heading 'Size|(Mb) ' justify left format 9,999,999.99
column tbused heading 'Used|(Mb) ' justify right format 9,999,999.99
column tbfree heading 'Free|(Mb) ' justify right format 9,999,999.99
column tbusedpct heading 'Used % ' justify left format a8
column tbfreepct heading 'Free % ' justify left format a8
break on report
compute sum label 'Totals:' of tbsize tbused tbfree on report
select t.tablespace_name, round(a.bytes,2) tbsize,
nvl(round(b.bytes,2),'0') tbused,
nvl(round(c.bytes,2),'0') tbfree,
to_char(round(100 * (nvl(b.bytes,0)/nvl(a.bytes,1)),2)) || '%' tbusedpct,
to_char(round(100 * (nvl(c.bytes,0)/nvl(a.bytes,1)),2)) || '%' tbfreepct
from dba_tablespaces t,
(select tablespace_name, round(sum(bytes)/1024/1024,2) bytes
from dba_data_files
group by tablespace_name
union
select tablespace_name, round(sum(bytes)/1024/1024,2) bytes
from dba_temp_files
group by tablespace_name ) a,
(select e.tablespace_name, round(sum(e.bytes)/1024/1024,2) bytes
from dba_segments e
group by e.tablespace_name
union
select tablespace_name, sum(max_size) bytes
from v$sort_segment
group by tablespace_name) b,
(select f.tablespace_name, round(sum(f.bytes)/1024/1024,2) bytes
from dba_free_space f
group by f.tablespace_name
union
select tmp.tablespace_name, (sum(bytes/1024/1024) - sum(max_size)) bytes
from dba_temp_files tmp, v$sort_segment sort
where tmp.tablespace_name = sort.tablespace_name
group by tmp.tablespace_name) c
where
t.tablespace_name = a.tablespace_name (+)
and t.tablespace_name = b.tablespace_name (+)
and t.tablespace_name = c.tablespace_name (+)
order by t.tablespace_name;