Oracle 11g EXP 導出空表失效問題：deferred_segment_creation 參數深度解析與三種解決方案

Oracle 11g EXP 導出空表失效問題：deferred_segment_creation 參數深度解析與三種解決方案

問題現象

Oracle 11g 使用傳統 exp 導出時，**空表 (無資料列) 無法導出**，導致遷移/備份不完整。

根本原因：11g 新特性 - 延遲段創建

exp 僅導出已分配 Segment 的物件，空表因無 Segment 被跳過。

驗證查詢

-- 為每個空表插入一筆再刪除，觸發 Segment 分配
BEGIN
  FOR t IN (SELECT table_name FROM user_tables WHERE num_rows = 0) LOOP
    EXECUTE IMMEDIATE 'INSERT INTO ' || t.table_name || ' VALUES (NULL)';  -- 需根據表結構調整
    EXECUTE IMMEDIATE 'ROLLBACK';
  END LOOP;
END;
/

-- 或手動執行 ALLOCATE EXTENT (更通用，不需構造 INSERT 值)
SELECT 'ALTER TABLE ' || table_name || ' ALLOCATE EXTENT;' 
  FROM user_tables 
 WHERE num_rows = 0;
-- 複製輸出結果在 SQL*Plus 執行

方案二：關閉延遲段創建 (針對未來新建表，需重啟) ⚙️

-- 源端導出
CREATE DIRECTORY expdp_dir AS '/backup/expdp';
GRANT READ, WRITE ON DIRECTORY expdp_dir TO source_user;

expdp source_user/password \
  DIRECTORY=expdp_dir \
  DUMPFILE=schema_%U.dmp \
  LOGFILE=expdp.log \
  SCHEMAS=source_user \
  PARALLEL=4;

-- 目標端導入
CREATE DIRECTORY impdp_dir AS '/backup/impdp';
GRANT READ, WRITE ON DIRECTORY impdp_dir TO target_user;

impdp target_user/password \
  DIRECTORY=impdp_dir \
  DUMPFILE=schema_%U.dmp \
  LOGFILE=impdp.log \
  REMAP_SCHEMA=source_user:target_user \
  TABLE_EXISTS_ACTION=REPLACE;

方案對比總結

最佳實踐建議

1. 新建資料庫即執行：

   ALTER SYSTEM SET deferred_segment_creation=FALSE SCOPE=SPFILE;
   SHUTDOWN IMMEDIATE; STARTUP;

2. 全面遷移 Data Pump：停用傳統 exp/imp，統一使用 expdp/impdp
3. 定期檢查：納入巡檢腳本，監控 USER_TABLES.NUM_ROWS=0 且 USER_SEGMENTS 無記錄的表
4. 異構遷移：Windows ↔ Linux、版本升級 (10g→11g→19c) 必須使用 Data Pump

ALLOCATE EXTENT 語法補充