Oracle 表空間使用情況查詢腳本集錦

Oracle 表空間使用情況查詢腳本集錦

掌握多種表空間使用率查詢方式，可因應不同監控需求。本文整理四種常用查詢腳本，涵蓋詳細使用率報表、自由空間統計、總容量查詢與簡易使用率計算。

1. 完整表空間使用率報表（含最大連續自由區塊）

SELECT UPPER(f.tablespace_name) "表空間名",
       d.tot_grootte_mb "表空間大小(M)",
       d.tot_grootte_mb - f.total_bytes "已使用空間(M)",
       TO_CHAR(ROUND((d.tot_grootte_mb - f.total_bytes)/d.tot_grootte_mb*100,2),'990.99') || '%' "使用比",
       f.total_bytes "空閒空間(M)",
       f.max_bytes "最大塊(M)"
FROM   (SELECT tablespace_name,
             ROUND(SUM(bytes)/(1024*1024),2) total_bytes,
             ROUND(MAX(bytes)/(1024*1024),2) max_bytes
       FROM   sys.dba_free_space
       GROUP  BY tablespace_name) f,
       (SELECT dd.tablespace_name,
             ROUND(SUM(dd.bytes)/(1024*1024),2) tot_grootte_mb
       FROM   sys.dba_data_files dd
       GROUP  BY dd.tablespace_name) d
WHERE  d.tablespace_name = f.tablespace_name
ORDER  BY 4 DESC;

優點：同時顯示最大連續自由區塊，利於判斷碎片嚴重度。

2. 表空間自由空間統計

SELECT tablespace_name,
       COUNT(*) extends,
       ROUND(SUM(bytes)/1024/1024,2) mb,
       SUM(blocks) blocks
FROM   dba_free_space
GROUP  BY tablespace_name;

用途：快速掌握各表空間自由區段數量與總可用空間。

3. 表空間總容量查詢

SELECT tablespace_name, SUM(bytes)/1024/1024 mb
FROM   dba_data_files
GROUP  BY tablespace_name;

4. 簡易使用率計算（JOIN 寫法）

SELECT total.tablespace_name,
       ROUND(total.mb,2) total_mb,
       ROUND(total.mb - free.mb,2) used_mb,
       ROUND((1 - free.mb/total.mb)*100,2) || '%' used_pct
FROM   (SELECT tablespace_name, SUM(bytes)/1024/1024 mb FROM dba_free_space GROUP BY tablespace_name) free,
       (SELECT tablespace_name, SUM(bytes)/1024/1024 mb FROM dba_data_files GROUP BY tablespace_name) total
WHERE  free.tablespace_name = total.tablespace_name;

進階監控建議

• 搭配 DBA_TABLESPACE_USAGE_METRICS（12c+）取得即時使用率指標
• 監控 MAX_BYTES 趨勢，若持續下降代表碎片化嚴重，需安排重組
• UNDO/TEMP 表空間另行查詢 DBA_UNDO_EXTENTS、V$TEMP_SPACE_HEADER
• 建立 Enterprise Manager 告警或自訂 Shell/Python 排程巡檢