Oracle 表空間縮減實戰：HWM 計算、RESIZE 指令產生與 TEMPFILE 處理

資料檔縮減 (RESIZE) 能回收空間、縮短備份時間，但受限於高水位 (HWM)。本文提供精準計算可縮減空間的 SQL、自動產生 ALTER DATABASE DATAFILE ... RESIZE 指令、暫存表空間 TEMPFILE 縮減邏輯，並說明 HWM 卡住時的降級處理。

核心原理

• HWM (High Water Mark)：資料檔中曾經使用過的最高區塊編號。RESIZE 不能低於 HWM。
• 可回收空間 = 檔案大小 - (HWM × 區塊大小)。
• 降低 HWM：ALTER TABLE ... MOVE、ALTER TABLE ... SHRINK SPACE (需 ENABLE ROW MOVEMENT)、ALTER INDEX ... REBUILD、EXPORT/IMPORT 或 Data Pump 重組。

1. 全庫資料檔可縮減空間掃描與指令產生

COL file_name FORMAT A60
COL current_mb FORMAT 999,999.99
COL resize_to_mb FORMAT 999,999.99
COL reclaim_mb FORMAT 999,999.99
COL resize_cmd FORMAT A80

SELECT 
  a.file_name,
  ROUND(a.bytes/1024/1024,2) AS current_mb,
  ROUND((b.hwm * a.block_size)/1024/1024,2) AS resize_to_mb,
  ROUND((a.bytes - b.hwm * a.block_size)/1024/1024,2) AS reclaim_mb,
  'ALTER DATABASE DATAFILE ''' || a.file_name || ''' RESIZE ' || 
   CEIL(b.hwm * a.block_size / 1024 / 1024) || 'M;' AS resize_cmd
FROM dba_data_files a
JOIN (
  SELECT file_id, MAX(block_id + blocks - 1) AS hwm
  FROM dba_extents
  GROUP BY file_id
) b ON a.file_id = b.file_id
WHERE (a.bytes - b.hwm * a.block_size) > 10*1024*1024  -- 可回收 > 10MB 才顯示
ORDER BY reclaim_mb DESC;

輸出範例：

FILE_NAME                                      CURRENT_MB RESIZE_TO_MB RECLAIM_MB RESIZE_CMD
---------------------------------------------- ---------- ------------ ---------- --------------------------------------------------
/u01/oradata/ORCL/users01.dbf                        5,120.00     1,200.00   3,920.00 ALTER DATABASE DATAFILE '/u01/oradata/ORCL/users01.dbf' RESIZE 1200M;

2. 指定表空間資料檔縮減指令產生

SELECT 
  a.file_name,
  ROUND(a.bytes/1024/1024,2) AS current_mb,
  ROUND((b.hwm * a.block_size)/1024/1024,2) AS resize_to_mb,
  'ALTER DATABASE DATAFILE ''' || a.file_name || ''' RESIZE ' || 
   CEIL(b.hwm * a.block_size / 1024 / 1024) || 'M;' AS resize_cmd
FROM dba_data_files a
JOIN (
  SELECT file_id, MAX(block_id + blocks - 1) AS hwm
  FROM dba_extents
  WHERE file_id IN (
    SELECT b.file_id FROM v$tablespace a, v$datafile b
    WHERE a.ts# = b.ts# AND a.name = UPPER('&tablespace_name')
  )
  GROUP BY file_id
) b ON a.file_id = b.file_id
WHERE (a.bytes - b.hwm * a.block_size) > 0
ORDER BY 4;

3. 暫存表空間 TEMPFILE 縮減 (單一 TEMPFILE 場景)

SELECT 
  'ALTER DATABASE TEMPFILE ''' || a.name || ''' RESIZE ' || b.siz || 'M;' AS resize_cmd
FROM v$tempfile a
CROSS JOIN (
  SELECT 
    NVL(MAX(segblk#), 128) * blk.value / 1024 / 1024 AS siz
  FROM v$sort_usage, 
       (SELECT value FROM v$parameter WHERE name = 'db_block_size') blk
) b;

邏輯：v$sort_usage.segblk# 記錄當前排序段使用的最高區塊，乘以 db_block_size 得到最小所需大小，保留 128 區塊基線。

4. 執行前驗證清單

• [ ] 目標檔案 **非 SYSTEM/SYSAUX/UNDO 核心檔案** (縮減核心檔案風險極高，需維護窗口並備份)。
• [ ] 確認 reclaim_mb > 0 且合理 (非負值)。
• [ ] 目標大小 CEIL(HWM * block_size / 1M) **大於 HWM**，建議再 **+5-10% 緩衝** (如 RESIZE 1300M 而非 1200M)。
• [ ] 在 **維護窗口** 執行，避開高峰期。
• [ ] 先在測試環境驗證指令語法與效果。

5. HWM 卡住時的降級處理流程

1. 識別佔用 HWM 附近區塊的段：
   SELECT owner, segment_name, segment_type FROM dba_extents WHERE file_id = &file_id AND block_id + blocks - 1 >= &hwm - 1000;
2. 若為 **表**：
     a. ALTER TABLE owner.tbl ENABLE ROW MOVEMENT;
     b. ALTER TABLE owner.tbl SHRINK SPACE COMPACT; (線上、不鎖表、調整 HWM)
     c. ALTER TABLE owner.tbl SHRINK SPACE; (釋放空間回檔案)
     d. ALTER TABLE owner.tbl DISABLE ROW MOVEMENT;
3. 若為 **索引**：ALTER INDEX owner.idx REBUILD ONLINE;
4. 若為 **LOB**：ALTER TABLE owner.tbl MODIFY LOB (col) (SHRINK SPACE); (11g+)
5. 重新執行 Phase 1 掃描，確認 HWM 已下降，再執行 RESIZE。

6. 自動化縮減腳本框架 (Shell + SQL*Plus)

#!/bin/bash
# shrink_datafiles.sh - 每月執行，需 DBA 覆核輸出指令後人工執行
ORACLE_SID=ORCL
THRESHOLD_MB=100  # 僅處理可回收 > 100MB 的檔案
OUTPUT=/tmp/resize_cmds_$(date +%Y%m%d).sql

sqlplus -s / as sysdba < $OUTPUT
SET HEAD OFF FEED OFF PAGES 0 LINES 200
SELECT 'ALTER DATABASE DATAFILE ''' || a.file_name || ''' RESIZE ' || 
       (CEIL(b.hwm * a.block_size / 1024 / 1024) + 50) || 'M;'  -- +50MB 緩衝
FROM dba_data_files a
JOIN (
  SELECT file_id, MAX(block_id + blocks - 1) AS hwm
  FROM dba_extents
  WHERE segment_type NOT IN ('ROLLBACK','TYPE2 UNDO')  -- 排除 UNDO 段
  GROUP BY file_id
) b ON a.file_id = b.file_id
WHERE (a.bytes - b.hwm * a.block_size) / 1024 / 1024 > $THRESHOLD_MB
  AND a.tablespace_name NOT IN ('SYSTEM','SYSAUX','UNDOTBS1');  -- 排除核心表空間
EXIT
EOF

echo "Generated resize commands in $OUTPUT"
echo "REVIEW CAREFULLY BEFORE EXECUTION!"
cat $OUTPUT