检查表空间使用率SQL语句

临时表空间除外：
select total.tablespace_name,
       round(total.MB,2) as Total_MB,
       round(total.MB-free.MB, 2) as Used_MB,
       round((1-free.MB/total.MB)*100, 2) as Used_Pct
from
    (select tablespace_name,
        sum(bytes)/1024/1024 as MB
     from dba_free_space
     group by tablespace_name) free,
    (select tablespace_name,
    sum(bytes)/1024/1024 as MB
    from dba_data_files
    group by tablespace_name) total
where free.tablespace_name=total.tablespace_name;


所有表空间：
SELECT df.tablespace_name as "Tablespace",
       tf.bytes / 1024 / 1024 as "Size (MB)",
       round(df.bytes_used / 1024 / 1024, 2) as "Used (MB)",
       round(df.bytes_used / tf.bytes * 100, 2) as "% Used"
  FROM dba_temp_files tf,
       (SELECT tablespace_name, bytes_used
          FROM v$temp_space_header
         GROUP BY tablespace_name, bytes_used) df
 WHERE tf.tablespace_name(+) = df.tablespace_name
UNION ALL
SELECT df.tablespace_name,
       df.bytes / 1024 / 1024,
       round((df.bytes - SUM(fs.bytes)) / 1024 / 1024,2),
       round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes, 2)
  FROM dba_free_space fs,
       (SELECT tablespace_name, SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
 WHERE fs.tablespace_name(+) = df.tablespace_name
 GROUP BY df.tablespace_name, df.bytes
 ORDER BY 1 ASC;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26194851/viewspace-750176/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26194851/viewspace-750176/