Oracle 表空間大小監控與擴充實務指南

Oracle 表空間大小監控與擴充實務指南

磁碟空間不足是 DBA 最常面對的挑戰。本文提供完整的表空間使用率查詢、數據檔案擴充性檢查、新增數據檔案與驗證的標準作業流程。

1. 查詢表空間使用率（核心監控 SQL）

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) "Pct_Used"
FROM   (SELECT tablespace_name, SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) a,
       (SELECT tablespace_name, SUM(bytes) bytes 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;

欄位說明：

• Sum_MB：所有數據檔案在 OS 層面佔用總大小
• Used_MB：已使用空間
• Free_MB：剩餘可用空間
• Pct_Used：使用率百分比

2. 檢查數據檔案自動擴充上限

當某表空間（如 MLOG_NORM_SPACE）使用率超過 90%，需確認檔案數量與擴充上限：

SELECT file_name, tablespace_name,
       bytes/1024/1024 "Bytes_MB",
       maxbytes/1024/1024 "MaxBytes_MB"
FROM   dba_data_files
WHERE  tablespace_name = 'MLOG_NORM_SPACE';

3. 取得表空間建立 DDL（用於複製檔案路徑與參數）

SELECT dbms_metadata.get_ddl('TABLESPACE','MLOG_NORM_SPACE') FROM dual;

4. 作業系統層面確認磁碟剩餘空間

Unix/Linux 環境：df -g（或 df -h）查看可用磁碟容量。

5. 新增數據檔案

ALTER TABLESPACE MLOG_NORM_SPACE
ADD DATAFILE '/Oracle/oms/oradata/mlog/Mlog_Norm_data001.dbf'
SIZE 10M AUTOEXTEND ON MAXSIZE 20G;

6. 驗證新增檔案

SELECT file_name, file_id, tablespace_name
FROM   dba_data_files
WHERE  tablespace_name = 'MLOG_NORM_SPACE';

7. 移除數據檔案（謹慎操作）

ALTER TABLESPACE MLOG_NORM_SPACE
DROP DATAFILE '/Oracle/oms/oradata/mlog/Mlog_Norm_data001.dbf';

注意：檔案必須為空（無區段）才能移除，且表空間至少保留一個數據檔案。

最佳實務建議

1. 建立定時排程監控使用率，閾值 80% 告警、90% 緊急處理
2. 數據檔案預設啟用 AUTOEXTEND ON MAXSIZE UNLIMITED（受限於磁碟配額）
3. 定期執行 ALTER TABLESPACE ... COALESCE 整理碎片
4. 大型表空間採用多數據檔案分佈於不同磁碟，提升 I/O 平行度