Oracle 表空間佔用查詢實戰：精準統計表、索引、分區表大小

前言

掌握 Oracle 對象空間佔用情況是容量規劃、性能調優、存儲成本控制的基礎。本文提供從基礎查詢到生產可用腳本的完整方案，解決引號轉義、空行干擾、分區表識別等實戰痛點。

一、核心數據字典視圖

二、基礎統計查詢

1. 用戶所有段空間統計（MB）

SELECT segment_name,
       segment_type,
       ROUND(SUM(bytes)/1024/1024, 2) AS size_mb
FROM user_segments
GROUP BY segment_name, segment_type
ORDER BY size_mb DESC;

2. 僅統計表佔用空間

SELECT segment_name,
       ROUND(SUM(bytes)/1024/1024, 2) AS size_mb
FROM user_segments
WHERE segment_type = 'TABLE'
GROUP BY segment_name
ORDER BY size_mb DESC;

3. 索引空間統計

SELECT segment_name,
       ROUND(SUM(bytes)/1024/1024, 2) AS size_mb
FROM user_segments
WHERE segment_type = 'INDEX'
GROUP BY segment_name
ORDER BY size_mb DESC;

4. 分區表空間統計

SELECT segment_name,
       ROUND(SUM(bytes)/1024/1024, 2) AS size_mb
FROM user_segments
WHERE segment_type = 'TABLE PARTITION'
GROUP BY segment_name
ORDER BY size_mb DESC;

三、動態生成逐表查詢腳本（解決引號轉義）

問題：單引號嵌套在動態 SQL 中的處理

Oracle 中單引號轉義有兩種方式：

• 兩個單引號：`'I''m'` → `I'm`
• CHR(39)：`'I' || CHR(39) || 'm'` → `I'm`

生成逐表統計腳本（CHR 方式，可讀性更好）

SET LINESIZE 300
SET HEAD OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SPOOL gen_table_size.sql

SELECT 'SELECT ''' || segment_name || ''' AS table_name, ' ||
       'ROUND(SUM(bytes)/1024/1024, 2) AS size_mb ' ||
       'FROM user_segments ' ||
       'WHERE segment_type = ' || CHR(39) || 'TABLE' || CHR(39) ||
       ' AND segment_name = ' || CHR(39) || segment_name || CHR(39) ||
       ' GROUP BY segment_name;'
FROM user_segments
WHERE segment_type = 'TABLE';

SPOOL OFF

-- 執行生成的腳本
@gen_table_size.sql

輸出結果導入 Excel 處理空行

1. SQL*Plus 執行後輸出文本文件
2. Excel 打開 → 數據 → 從文本/CSV
3. 使用「自動篩選」刪除空行
4. 插入求和行統計總空間

四、進階：含表空間、擁有者的全庫統計

SELECT owner,
       tablespace_name,
       segment_type,
       segment_name,
       ROUND(SUM(bytes)/1024/1024/1024, 2) AS size_gb
FROM dba_segments
WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'INDEX', 'INDEX PARTITION', 'LOB', 'LOB PARTITION')
GROUP BY owner, tablespace_name, segment_type, segment_name
ORDER BY size_gb DESC;

五、實用技巧總結

六、自動化監控建議

• 建立每日統計作業，寫入歷史表 `TABLE_SIZE_HIST`
• 設置閾值告警：單表 > 50GB 或 日增長 > 5%
• 結合 `DBA_FREE_SPACE` 預測表空間耗盡時間
• 定期輸出 Top N 報告發送給應用擁有者

結語

空間統計看似簡單，但引號轉義、分區識別、結果清洗等細節決定了腳本在生產環境的可用性。建議將上述查詢封裝為標準化監控腳本，納入日常巡檢體系，做到「心中有數，規劃有據」。