Oracle 全局臨時表數據消失之謎：ON COMMIT DELETE vs PRESERVE ROWS

現象複現

場景：事務中向全局臨時表 tmp1 插入數據 → 循環創建另一臨時表 tmp2（DDL 操作） → 查詢 tmp1 發現數據消失。

原建表語句：

CREATE GLOBAL TEMPORARY TABLE crm_t_11961 (
  cust_id VARCHAR(30),
  cust_name VARCHAR(100)
) ON COMMIT DELETE ROWS;

根因分析

DDL 隱式提交：Oracle 中任何 DDL 語句（CREATE TABLE、DROP、TRUNCATE、ALTER 等）執行前會自動提交當前事務。

ON COMMIT DELETE ROWS：臨時表定義為事務級，每次提交後 Oracle 自動截斷表（刪除所有行）。

因此：創建 tmp2 觸發隱式提交 → 事務結束 → tmp1 數據被清空。

解決方案

CREATE GLOBAL TEMPORARY TABLE crm_t_11961 (
  cust_id VARCHAR(30),
  cust_name VARCHAR(100)
) ON COMMIT PRESERVE ROWS;

ON COMMIT PRESERVE ROWS：臨時表為會話級，數據在會話結斷（斷開連接）時才清空，跨事務保留。

兩種模式對比

最佳實踐建議

1. 默認選擇 PRESERVE ROWS：除非明確需要事務級隔離，否則會話級更靈活，避免隱式提交導致的數據丟失驚喜。
2. 避免在同一事務中混合 DML 與 DDL：若必須動態建表，考慮：
   • 預先創建好所有臨時表（部署階段）。
   • 使用 EXECUTE IMMEDIATE 在獨立事務中執行 DDL（PRAGMA AUTONOMOUS_TRANSACTION），但需謹慎設計。
   • 改用永久表 + 定期清理任務，或使用 DBMS_SESSION 上下文變量傳遞中間結果。
3. 統計信息：全局臨時表無統計信息，優化器使用默認值，大數據量時考慮 DBMS_STATS.GATHER_TABLE_STATS（僅對當前會話有效）或動態採樣 /*+ DYNAMIC_SAMPLING(4) */。
4. 索引：可在臨時表上創建索引（定義在創建語句中），同樣遵循 ON COMMIT 行為。

驗證腳本

-- 會話級測試
CREATE GLOBAL TEMPORARY TABLE gtt_session (id NUMBER) ON COMMIT PRESERVE ROWS;
INSERT INTO gtt_session VALUES (1);
COMMIT;
SELECT * FROM gtt_session; -- 返回 1 行

-- 事務級測試
CREATE GLOBAL TEMPORARY TABLE gtt_txn (id NUMBER) ON COMMIT DELETE ROWS;
INSERT INTO gtt_txn VALUES (1);
COMMIT;
SELECT * FROM gtt_txn; -- 返回 0 行