Oracle查詢表空間使用情況（經典篇）

　　查詢表空間使用情況
SELECT UPPER(FTABLESPACE_NAME) 表空間名
       DTOT_GROOTTE_MB 表空間大小(M)
       DTOT_GROOTTE_MB FTOTAL_BYTES 已使用空間(M)
       TO_CHAR(ROUND((DTOT_GROOTTE_MB FTOTAL_BYTES) / DTOT_GROOTTE_MB * )) || % 使用比
       FTOTAL_BYTES 空閒空間(M)
       FMAX_BYTES 最大塊(M)
FROM (SELECT TABLESPACE_NAME
               ROUND(SUM(BYTES) / ( * ) ) TOTAL_BYTES
               ROUND(MAX(BYTES) / ( * ) ) MAX_BYTES
          FROM SYSDBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F
       (SELECT DDTABLESPACE_NAME
               ROUND(SUM(DDBYTES) / ( * ) ) TOT_GROOTTE_MB
          FROM SYSDBA_DATA_FILES DD
         GROUP BY DDTABLESPACE_NAME) D
WHERE DTABLESPACE_NAME = FTABLESPACE_NAME
ORDER BY

　　查詢表空間的free space
select tablespace_name
       count(*) as extends
       round(sum(bytes) / / ) as MB
       sum(blocks) as blocks
from dba_free_space
group by tablespace_name;

　　查詢表空間的總容量
select tablespace_name sum(bytes) / / as MB
from dba_data_files
group by tablespace_name;

　　查詢表空間使用率
select totaltablespace_name
       round(totalMB ) as Total_MB
       round(totalMB freeMB ) as Used_MB
       round(( freeMB / totalMB) * ) || % as Used_Pct
from (select tablespace_name sum(bytes) / / as MB
          from dba_free_space
         group by tablespace_name) free
       (select tablespace_name sum(bytes) / / as MB
          from dba_data_files
         group by tablespace_name) total
where freetablespace_name = totaltablespace_name;

From:http://tw.wingwit.com/Article/program/Oracle/201311/17138.html