Oracle 表空間管理實戰：監控、擴展、自動擴展與碎片整理

表空間容量管理是 DBA 核心職責。本文提供生產環境實用的監控查詢、資料檔手動/自動擴展指令、表空間層級自動擴展設定、新增資料檔最佳實踐，助力構建「零驚訝」的儲存運維體系。

1. 表空間使用率即時監控查詢 (核心儀表板 SQL)

COL tablespace_name FORMAT A20
COL total_mb FORMAT 999,999.99
COL used_mb FORMAT 999,999.99
COL pct_used FORMAT 990.99 HEADING "USED%"
COL free_mb FORMAT 999,999.99
COL max_free_mb FORMAT 999,999.99 HEADING "MAX_FREE"

SELECT 
  UPPER(f.tablespace_name) AS tablespace_name,
  d.total_mb,
  d.total_mb - f.free_mb AS used_mb,
  ROUND((d.total_mb - f.free_mb) / d.total_mb * 100, 2) AS pct_used,
  f.free_mb,
  f.max_free_mb
FROM (
  SELECT tablespace_name,
         ROUND(SUM(bytes)/1024/1024,2) AS free_mb,
         ROUND(MAX(bytes)/1024/1024,2) AS max_free_mb
  FROM dba_free_space
  GROUP BY tablespace_name
) f
JOIN (
  SELECT tablespace_name,
         ROUND(SUM(bytes)/1024/1024,2) AS total_mb
  FROM dba_data_files
  GROUP BY tablespace_name
) d ON d.tablespace_name = f.tablespace_name
ORDER BY pct_used DESC;

告警閾值建議：USED% > 80% 觸發預警；> 90% 觸發緊急擴容流程。

2. 資料檔手動縮減/擴展

縮減至實際高水位 (HWM) 以上

-- 先查詢可縮減空間
SELECT file_name,
       ROUND(bytes/1024/1024) AS current_mb,
       ROUND((bytes - (MAX(block_id+blocks-1)*block_size))/1024/1024) AS reclaimable_mb
FROM dba_data_files
JOIN dba_extents USING (file_id)
GROUP BY file_id, file_name, bytes
HAVING (bytes - MAX(block_id+blocks-1)*block_size) > 10*1024*1024;  -- 可回收 >10MB

-- 執行縮減 (以實際 HWM 為準，留 5% 餘裕)
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/users01.dbf' RESIZE 5000M;

注意：若 HWM 靠近檔案尾端，需先 ALTER TABLE ... MOVE 或 SHRINK SPACE 降低 HWM 再縮減。

擴展資料檔

-- 指定大小擴展
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/app_data01.dbf' RESIZE 20G;

-- 開啟自動擴展
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/app_data01.dbf'
AUTOEXTEND ON NEXT 1G MAXSIZE 50G;

3. 表空間層級自動擴展 (10g+)

對 **Bigfile Tablespace** 或單一資料檔表空間生效：

ALTER TABLESPACE app_data AUTOEXTEND ON NEXT 1G MAXSIZE 100G;

小檔案表空間需逐一資料檔設定 AUTOEXTEND。

4. 新增資料檔擴容 (跨磁碟分散 I/O)

ALTER TABLESPACE app_data
ADD DATAFILE 
  '/u02/oradata/ORCL/app_data02.dbf' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 50G,
  '/u03/oradata/ORCL/app_data03.dbf' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 50G;

最佳實踐：新檔案置於不同 ASM Disk Group / LUN / 實體磁碟，平衡 I/O 與風險。

5. 暫存表空間管理

-- 監控暫存表空間使用
SELECT tablespace_name,
       ROUND(SUM(bytes_used)/1024/1024) AS used_mb,
       ROUND(SUM(bytes_free)/1024/1024) AS free_mb
FROM v$temp_space_header
GROUP BY tablespace_name;

-- 新增暫存檔
ALTER TABLESPACE temp ADD TEMPFILE '/u02/oradata/ORCL/temp02.dbf' SIZE 5G AUTOEXTEND ON NEXT 1G MAXSIZE 20G;

6. UNDO 表空間監控與保留調整

-- 監控 UNDO 使用狀況
SELECT 
  ROUND(SUM(undoblks)*8192/1024/1024) AS undo_mb,
  ROUND(MAX(tuned_undoretention)/60) AS tuned_retention_min
FROM v$undostat
WHERE begin_time > SYSDATE - 1/24;  -- 近 1 小時

-- 調整保留時間 (視 UNDO 表空間大小而定)
ALTER SYSTEM SET UNDO_RETENTION = 3600 SCOPE=BOTH;  -- 1 小時
-- 若需保證不被覆寫 (防 ORA-01555)
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

7. 碎片整理與高水位降低

• 表移動：ALTER TABLE big_table MOVE TABLESPACE app_data; (需重建索引)。
• 啟用列移動 + Shrink：
  ALTER TABLE big_table ENABLE ROW MOVEMENT;
ALTER TABLE big_table SHRINK SPACE COMPACT; (線上、不鎖表、調整 HWM)。
• 索引重建：ALTER INDEX idx_name REBUILD ONLINE;。

自動化監控腳本範例 (Shell + SQL*Plus)

#!/bin/bash
# check_tbs_usage.sh - 每小時排程
ORACLE_SID=ORCL
THRESHOLD=85
MAIL_TO="dba@example.com"

sqlplus -s / as sysdba < $THRESHOLD;
SPOOL OFF
EXIT
EOF

if [ -s /tmp/tbs_alert.lst ]; then
  mail -s "ALERT: Tablespace Usage > ${THRESHOLD}% on $ORACLE_SID" $MAIL_TO < /tmp/tbs_alert.lst
fi