Oracle 11.2.0.4 deferred_segment_creation 參數詳解：EXTENT 延遲分配機制、EXP 導出缺失對象問題與三種解決方案

Oracle 延遲段創建特性與 EXP 導出陷阱完整指南

Oracle 11.2.0.4 引入 deferred_segment_creation 參數（預設 TRUE），改變空表/分區的空間分配行為：無數據時不分配 Segment/Extent。此特性節省空間但導致傳統 EXP 導出時跳過無 Segment 的對象，造成導出不完整。本文詳解機制、影響範圍與三種解決方案。

一、參數行為詳解

SYS@ORCL> SHOW PARAMETER segment
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE

二、檢測對象是否已分配 Segment

-- 方法 1：查看 DBA_SEGMENTS（無行 = 未分配）
SELECT owner, segment_name, segment_type, tablespace_name, bytes
FROM dba_segments
WHERE segment_name = 'T1';

-- 方法 2：DBMS_METADATA 顯示 DDL（含 SEGMENT CREATION 子句）
SET LONG 99999
SELECT dbms_metadata.get_ddl('TABLE', 'T1') FROM dual;
-- 輸出包含 "SEGMENT CREATION DEFERRED" 或 "SEGMENT CREATION IMMEDIATE"

三、EXP 導出缺失對象根因

傳統 EXP 依賴 DBA_SEGMENTS 判斷對象是否存在，無 Segment 的表/索引/分區被視為不存在而跳過。Data Pump (EXPDP) 不受影響，因其讀取字典元數據而非段視圖。

四、三種解決方案

方案 1：關閉參數（僅影響後續建表）

ALTER SYSTEM SET deferred_segment_creation = FALSE SCOPE = BOTH;
-- 或 PFILE/SPFILE 修改後重啟

⚠️ 已建空表不會自動分配 Segment，需配合方案 2/3 處理存量。

方案 2：建表時顯式指定立即分配

CREATE TABLE t1 (id NUMBER) SEGMENT CREATION IMMEDIATE;
-- 覆蓋參數預設行為，強制分配初始 Extent

方案 3：為現有無 Segment 對象強制分配

-- 方法 A：分配一個 Extent（推薦，無需數據）
ALTER TABLE t1 ALLOCATE EXTENT;

-- 方法 B：插入一行再刪除（產生 Undo/Redo）
INSERT INTO t1 VALUES (1);
COMMIT;
DELETE FROM t1 WHERE id = 1;
COMMIT;

-- 批次處理腳本
BEGIN
  FOR obj IN (SELECT owner, segment_name FROM dba_tables
              WHERE owner NOT IN ('SYS','SYSTEM')
                AND NOT EXISTS (SELECT 1 FROM dba_segments s
                                WHERE s.owner = dba_tables.owner
                                  AND s.segment_name = dba_tables.table_name))
  LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE ' || obj.owner || '.' || obj.segment_name || ' ALLOCATE EXTENT';
  END LOOP;
END;
/

五、最佳實踐建議

1. 新建資料庫建議保持 TRUE（節省空間、加速建表），但導出備份必須使用 EXPDP。
2. 若必須使用傳統 EXP，安裝後立即設為 FALSE，並對存量空表執行 ALLOCATE EXTENT。
3. 分區表同理：ALTER TABLE ... MODIFY PARTITION p1 ALLOCATE EXTENT。
4. 監控腳本：定期掃描 DBA_TABLES vs DBA_SEGMENTS 差集，自動分配或報警。