Le Blog de DiabloCorps

select
    a.TABLESPACE_NAME,
    round(((nvl(sum(b.bytes),0)-nvl(sum(c.free_bytes),0)) / nvl(sum(b.maxbytes),0))*100, 2)||' %' "% Utilisation",
    --a.EXTENT_MANAGEMENT,
    --a.ALLOCATION_TYPE,
    --a.BIGFILE,
    nvl(sum(b.bytes),0)/(1024*1024)||' Mo' "Taille",
    nvl(sum(b.maxbytes),0)/(1024*1024)||' Mo' "Taille Max",
    round((nvl(sum(b.bytes),0)-nvl(sum(c.free_bytes),0))/(1024*1024),1)||' Mo'  "Utilisés",
    nvl(sum(b.count_files),0) "Nb fichiers",
    a.CONTENTS,
    a.SEGMENT_SPACE_MANAGEMENT,
    a.STATUS "Statut"
from DBA_TABLESPACES a,
    (
    select TABLESPACE_NAME,
        sum(BYTES) bytes,
        count(*) count_files,
        sum(greatest(MAXBYTES,BYTES)) maxbytes
    from DBA_DATA_FILES
    group by TABLESPACE_NAME
    union all
    select TABLESPACE_NAME,
        sum(BYTES),
        count(*),
        sum(greatest(MAXBYTES,BYTES)) maxbytes
    from DBA_TEMP_FILES
    group by TABLESPACE_NAME
    ) b,
    (
    select TABLESPACE_NAME,
        sum(BYTES) free_bytes
    from DBA_FREE_SPACE
    group by TABLESPACE_NAME
    union all
    select TABLESPACE_NAME,
        sum(BYTES_FREE) free_bytes
    from V$TEMP_SPACE_HEADER
    group by TABLESPACE_NAME
    ) c
where a.TABLESPACE_NAME = b.TABLESPACE_NAME (+)
    and a.TABLESPACE_NAME = c.TABLESPACE_NAME (+)
group by
    a.TABLESPACE_NAME,
    a.CONTENTS,
    a.EXTENT_MANAGEMENT,
    a.ALLOCATION_TYPE,
    a.SEGMENT_SPACE_MANAGEMENT,
    a.BIGFILE,
    a.STATUS
order by a.TABLESPACE_NAME;