ORACLE: VERIFIER L'OCCUPATION D'UN TABLESPACE

SET PAUSE ON
SET PAUSE 'Press Return to continue'
SET PAGESIZE 300
SET LINESIZE 400


COL "Tablespace Name" FORMAT a20
col "File Name" FORMAT a80

select    substr(df.tablespace_name,1,20) "Tablespace Name",
              substr(df.file_name,1,80) "File Name",
             round(df.bytes/1024/1024,0) "Size (M)"
             decode(e.used_bytes,NULL,0, round(e.used_bytes/1024/1024,0)) "Used (M)",
            decode(f.free_bytes,NULL,0, round(f.free_bytes/1024/1024,0) "Free (M)",
            decode(f.free_bytes,NULL,0, round(f.free_bytes/1024/1024,0) "% Used"

FROM DBA_DATA_FILES DF,
           ( SELECT file_id,sum(bytes) used_bytes
             FROM dba_extents
             GROUP BY file_id) E,
          ( SELECT sum(bytes) free_bytes, file_id
            FROM dba_free_space
           GROUP BY file_id) F
WHERE e.file_id (+) = df.file_id
AND      df.file_id = f.file_id(+)
ORDER BY df.tablespace_name, df.file_name;