Oracle Datafile 管理全攻略：新增、自動擴展、Resize、Bigfile/Smallfile、Tempfile 實戰指令

Oracle 資料檔 (Datafile) 完整生命週期管理

表空間由一或多個 Datafile 組成。本文整理：建立表空間指定 Datafile、新增 Datafile/Tempfile、啟用/關閉自動擴展、手動 Resize、Bigfile vs Smallfile 選擇、OMF (Oracle Managed Files) 簡化管理等核心操作。

建立表空間時指定 Datafile

-- Smallfile (傳統，多檔案)
CREATE SMALLFILE TABLESPACE STAGING
DATAFILE '/u01/app/oracle/oradata/orcl/staging01.dbf' SIZE 3M REUSE
NOLOGGING
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

-- Bigfile (單一超大檔案，適合超大表空間)
CREATE BIGFILE TABLESPACE BIG_TBS
DATAFILE '/u01/app/oracle/oradata/orcl/big_tbs01.dbf' SIZE 100G
AUTOEXTEND ON NEXT 10G MAXSIZE UNLIMITED;

新增 Datafile / Tempfile

-- 永久表空間新增 Datafile
ALTER TABLESPACE MYTS01
ADD DATAFILE '/oracle/oradata/orcl/myts03.dbf' SIZE 10M;

-- 臨時表空間新增 Tempfile
ALTER TABLESPACE TEMP
ADD TEMPFILE '/oracle/oradata/orcl/mytemp.dbf' SIZE 10M;

自動擴展 (AUTOEXTEND) 管理

-- 建立時啟用
CREATE TABLESPACE MYTS02
DATAFILE '/oracle/oradata/orcl/myts02_1.dbf' SIZE 10M
AUTOEXTEND ON NEXT 5M MAXSIZE 50M;

-- 新增檔案時啟用
ALTER TABLESPACE MYTS02
ADD DATAFILE '/oracle/oradata/orcl/myts02_2.dbf' SIZE 10M
AUTOEXTEND ON NEXT 4K MAXSIZE 40M;

-- 修改現有檔案啟用/調整自動擴展
ALTER DATABASE
DATAFILE '/oracle/oradata/orcl/myts01_3.dbf'
AUTOEXTEND ON NEXT 2M MAXSIZE 20M;

-- 關閉自動擴展
ALTER DATABASE
DATAFILE '/oracle/oradata/orcl/myts01_3.dbf'
AUTOEXTEND OFF;

手動調整檔案大小 (RESIZE)

-- 擴大 (不可小於已使用空間)
ALTER DATABASE
DATAFILE '/oracle/oradata/orcl/myts01_3.dbf'
RESIZE 100M;

-- 縮小 (需先 SHRINK SPACE 或移動段)
-- ALTER TABLESPACE ... SHRINK SPACE; (11g+)

Bigfile vs Smallfile 選擇指南

OMF (Oracle Managed Files) 簡化管理

-- 啟用 OMF (參數檔)
DB_CREATE_FILE_DEST = '/u01/app/oracle/oradata'
DB_CREATE_ONLINE_LOG_DEST_1 = '/u01/app/oracle/oradata'

-- 建表空間無需指定檔案路徑
CREATE TABLESPACE OMF_TBS;  -- 自動在 DB_CREATE_FILE_DEST 建檔
ALTER TABLESPACE OMF_TBS ADD DATAFILE; -- 自動新增

查看 Datafile 資訊

-- 所有 Datafile
SELECT FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024 MB, AUTOEXTENSIBLE, MAXBYTES/1024/1024 MAX_MB
FROM DBA_DATA_FILES;

-- Tempfile
SELECT FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024 MB, AUTOEXTENSIBLE
FROM DBA_TEMP_FILES;

-- 空間使用狀況
SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 TOTAL_MB,
       SUM(DECODE(AUTOEXTENSIBLE,'YES',MAXBYTES,BYTES))/1024/1024 MAX_MB
FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME;

最佳實踐建議

1. 預設啟用 AUTOEXTEND + MAXSIZE：避免 ORA-01652 表空間滿，同時設上限防止單一檔案佔滿磁碟
2. 定期監控：DBA_FREE_SPACE、DBA_DATA_FILES 設定告警閾值 (如 80%)
3. ASM 環境：優先使用 Bigfile + OMF，讓 ASM 管理條帶化與鏡像
4. 備份前檢查：確認所有 Datafile ONLINE、無 OFFLINE/RECOVER 狀態
5. 文件化：記錄每次 ADD/RESIZE/AUTOEXTEND 變更原因與時間