Oracle表空间监控与利用率分析

select c.tablespace_name tablespace,
       round(a.bytes / 1048576,2) all_MB,
       round(b.bytes / 1048576,2) free_MB,
       round((a.bytes - b.bytes) / 1048576,2) used_MB,
       round( b.bytes / a.bytes * 100,2) pct_free,
       round((a.bytes - b.bytes) / a.bytes * 100,2) pct_used,
       c.tablespace_name tablespace
  from (select tablespace_name,
               sum(a.bytes) bytes,
               min(a.bytes) minbytes,
               max(a.bytes) maxbytes
          from sys.dba_data_files a
         group by tablespace_name) a,
       (select a.tablespace_name, nvl(sum(b.bytes), 0) bytes
          from sys.dba_data_files a, sys.dba_free_space b
         where a.tablespace_name = b.tablespace_name(+)
           and a.file_id = b.file_id(+)
         group by a.tablespace_name) b,
       sys.dba_tablespaces c
 where a.tablespace_name = b.tablespace_name(+)
   and a.tablespace_name = c.tablespace_name
union all
SELECT d.tablespace_name tablespace_name,
       NVL(u.bytes, 0)/1048576 megs_alloc_MB,
       NVL(f.bytes, 0)/1048576 megs_free_MB,
       (NVL(u.bytes, 0) - NVL(f.bytes, 0))/1048576 megs_used_MB,
       round(100*NVL(f.bytes, 0)/NVL(u.bytes, 0),2) pct_free,
       round(100-100*(NVL(f.bytes, 0))/NVL(u.bytes, 0),2) pct_used,
       d.tablespace_name tablespace_name
  FROM dba_tablespaces d,