Oracle 11g 新特性 ENABLE_DDL_LOGGING 參數實測與應用指南

參數基本資訊

記錄的 DDL 類型

參考官方文檔，涵蓋以下物件的 CREATE/ALTER/DROP/TRUNCATE/RENAME：

• CLUSTER, FUNCTION, INDEX, OUTLINE, PACKAGE, PACKAGE BODY, PROCEDURE, PROFILE, SEQUENCE
• SYNONYM (CREATE/DROP), TABLE (含 RENAME/TRUNCATE), TRIGGER, TYPE, TYPE BODY
• USER (DROP), VIEW

已知 Bug 警示

Bug 12938609：11.2.0.2 和 11.1.0.7 版本中，RENAME TABLE 語句不會被記錄。

• 修復版本：12.1.0.1 (Base)、11.2.0.4 (Server Patch Set)
• 若運行受影響版本，請升級或打補丁。

實測驗證 (Oracle 11.2.0.4.0)

1. 開啟參數

SQL> show parameter ddl
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout                     integer     0
enable_ddl_logging                   boolean     FALSE

SQL> alter system set enable_ddl_logging=true;
System altered.

SQL> show parameter ddl
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout                     integer     0
enable_ddl_logging                   boolean     TRUE

2. 執行測試 DDL

SQL> show user
USER is "LC0029999"

SQL> create table t1 as select * from dba_objects where 1=2;
Table created.

SQL> alter table t1 rename to t11;
Table altered.

SQL> create table lc0039999.t1 as select * from dba_objects where 1=2;
Table created.

3. Alert Log 記錄結果

Wed Jun 10 01:38:35 2015 ALTER SYSTEM SET enable_ddl_logging=TRUE SCOPE=BOTH;
Wed Jun 10 01:39:17 2015 create table t1 as select * from dba_objects where 1=2
Wed Jun 10 01:40:03 2015 alter table t1 rename to t11
Wed Jun 10 01:46:52 2015 create table lc0039999.t1 as select * from dba_objects where 1=2

關鍵觀察與限制

1. 純文本記錄：Alert Log 直接記錄執行的 SQL 文本，不包含執行用戶、Client IP、Session ID、執行耗時 等上下文資訊。
2. 跨 Schema 可見：其他用戶建表 (如 lc0039999.t1) 同樣被記錄，實現全庫級 DDL 審計。
3. RENAME TABLE 在 11.2.0.4 已修復：測試中 alter table t1 rename to t11 正確記錄。
4. 非 DDL 不記錄：DML (INSERT/UPDATE/DELETE)、查詢、PL/SQL 執行不在記錄範圍。
5. 動態生效：無需重啟實例，線上開關即時生效，適合臨時開啟排查。

生產環境應用建議

適用場景

• 變更管理審計：配合日誌收集系統, 定期導出 DDL 記錄對照變更單。
• 故障時間點定位：效能突變時, 快速查閱該時間窗口是否有 DDL 執行 (如索引建立/刪除、表截斷)。
• 合規檢查：滿足「資料庫結構變更留痕」的基礎合規要求。
• 開發/測試環境：零成本追蹤開發人員結構變更, 避免「誰改的表」扯皮。

局限與補充方案

最佳實務配置

-- 1. 永久生效 (SPFILE)
ALTER SYSTEM SET enable_ddl_logging=TRUE SCOPE=SPFILE;

-- 2. 確保 Alert Log 足夠大且被收集
-- log_archive_dest_1='LOCATION=/u01/app/oracle/admin/adump'  # 也收集 trace 檔

-- 3. 定期清理/歸檔 Alert Log, 避免佔滿 DIAG_DEST

版本遷移提醒

• 11.2.0.3 及以下：確認已打 Bug 12938609 補丁，否則 RENAME 不記錄。
• 12c 起：引入 Unified Auditing，提供更完整的 DDL 審計 (含用戶、客戶端、返回碼)，建議遷移至統一審計架構。
• 19c/21c：ENABLE_DDL_LOGGING 仍保留相容，但官方推薦使用 Unified Auditing。

總結

ENABLE_DDL_LOGGING=TRUE 是 Oracle 11g 提供的零代碼、零性能影響、全實例級的 DDL 記錄開關。雖缺乏上下文資訊，但作為第一道審計防線、故障排查快照極具價值。建議所有生產庫預設開啟，並配合日誌收集系統構建完整變更審計鏈路。