Oracle 表空間容量調整腳本實戰：縮容與 ASM 監控全流程

生產環境中，表空間長期運行易產生碎片與空間浪費。本文提供一套完整的「查詢 → 分析 → 生成縮容腳本 → ASM 磁盤組巡檢」自動化方案，助 DBA 安全回收閒置空間。

一、核心邏輯：三步走策略

1. 建立表空間水位表：統計各表空間總量、已用、剩餘、使用率，按使用率降序排列，優先處理高水位表空間。
2. 深入數據文件粒度：針對業務表空間（如 %CQLT%、%CQST%、TS%、IDX%、%HX%）且總量 > 100M 的文件，計算每個文件的實際已用空間。
3. 生成 RESIZE 腳本：在「已用空間 + 100M 安全餘量」基礎上重置文件大小，避免縮容過激導致 ORA-03297。

二、關鍵 SQL 腳本詳解

1. 表空間水位總覽表

CREATE TABLE system.tbs_detail AS
SELECT a.tablespace_name,
       a.bytes/1024/1024 "Sum_MB",
       (a.bytes-b.bytes)/1024/1024 "used_MB",
       b.bytes/1024/1024 "free_MB",
       ROUND(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
FROM (SELECT tablespace_name, SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) a,
     (SELECT tablespace_name, SUM(bytes) bytes, MAX(bytes) largest FROM dba_free_space GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
ORDER BY ((a.bytes-b.bytes)/a.bytes) DESC;

2. 業務數據文件空間明細

CREATE TABLE system.datafile_space AS
SELECT a.tablespace_name,
       a.file_name,
       a.bytes/1024/1024 total,
       b.sum_free/1024/1024 free
FROM dba_data_files a,
     (SELECT file_id, SUM(bytes) sum_free FROM dba_free_space GROUP BY file_id) b
WHERE a.file_id = b.file_id
  AND a.tablespace_name IN (
      SELECT tablespace_name FROM system.tbs_detail
      WHERE (tablespace_name LIKE '%CQLT%' OR tablespace_name LIKE '%CQST%'
             OR tablespace_name LIKE 'TS%' OR tablespace_name LIKE 'IDX%'
             OR tablespace_name LIKE '%HX%')
        AND "Sum_MB" > 100);

3. 生成縮容命令（每文件預留 100M）

SELECT 'ALTER DATABASE DATAFILE ''' || file_name || ''' RESIZE ' ||
       ROUND(total - free + 100, 0) || 'M;'
FROM system.datafile_space;

三、ASM 磁盤組巡檢腳本

配合 sqlplus / as sysasm 執行，輸出磁盤組名、狀態、類型、總/可用空間（MB），便於判斷是否需擴容或 Rebalance。

SET FEED OFF LINESIZE 200 PAGESIZE 200 ECHO OFF
SPOOL /home/oracle/check_log/chktbs.log APPEND
SELECT name, state, type, total_mb, free_mb FROM v$asm_diskgroup;
SPOOL OFF

四、執行建議與避坑指南

• 執行前必備份：腳本僅生成命令，需人工核對後在維護窗口執行。
• 監控 Resize 進度：SELECT * FROM v$session_longops WHERE opname LIKE 'Resize%';
• 防 ORA-03297：若報「file size would be too small」，說明文件末端有數據，需先 ALTER TABLE ... MOVE 或 SHRINK SPACE 移動高水位標記（HWM）。
• ASM 環境：Resize 實際操作磁盤組層面，需確保磁盤組有足夠可用空間吸收釋放的Extent。
• 定期清理歷史表：DROP TABLE system.tbs_detail, system.datafile_space; 避免 SYSAUX 膨脹。

將上述邏輯封裝為 Shell/Python 定時任務，配合企業監控平台告警，即可實現表空間容量的「自動感知、人工審核、安全縮容」閉環管理。