執行 DDL (CREATE/ALTER/DROP/TRUNCATE) 時，Oracle 自動獲取 DDL 鎖保護物件結構不被並發修改。理解三類鎖特性、隱式提交行為、解析鎖失效機制，是避免「DDL 阻塞 DML」、「DDL 導致游標失效」、「隱式提交破壞事務邊界」的關鍵。本文深度解析 DDL 鎖全生命週期。
-- 用戶執行
CREATE TABLE t (id NUMBER);
-- Oracle 內部轉換為 (簡化)
COMMIT; -- 隱式提交 1: 結束前序事務
INSERT INTO sys.tab$ ...; -- 記錄表定義
INSERT INTO sys.col$ ...; -- 記錄欄位定義
... -- 其他字典表操作
COMMIT; -- 隱式提交 2: 提交字典變更
-- 若 DDL 失敗 (如 ORA-00955 名稱已存在)，仍會執行隱式提交 2INSERT → DDL 失敗 → INSERT 已提交 (見文中 sess#1/sess#2 經典案例)COMMIT 或 ROLLBACK，明確事務邊界；生產環境 DDL 嚴禁在業務高峰期執行| 鎖類型 | 觸發操作 | 保護對象 | 相容性 | 對 DML 影響 |
|---|---|---|---|---|
| 排他 DDL 鎖 (Exclusive DDL Lock) | 大多數 DDL: CREATE/ALTER/DROP TABLE/INDEX/VIEW TRUNCATE TABLE ALTER TABLE MOVE/SPLIT/COALESCE | 物件結構 (表、索引、視圖等) | 與所有 DDL 鎖互斥 (含共享 DDL、解析鎖) | 阻塞所有 DML (INSERT/UPDATE/DELETE 等待) |
| 共享 DDL 鎖 (Shared DDL Lock) | CREATE VIEW CREATE SYNONYM COMPILE PROCEDURE/FUNCTION/PACKAGE CREATE MATERIALIZED VIEW LOG | 被引用的基表結構 | 與共享 DDL 鎖相容 (多會話可同時建視圖) 與排他 DDL 鎖互斥 | 允許 DML (基表可正常增刪改) 但阻塞基表結構變更 (ALTER TABLE 等待) |
| 可中斷解析鎖 (Breakable Parse Lock) | SQL 游標解析 PL/SQL 編譯/執行 | 被引用物件的解析表示 (Shared Pool 中) | 與所有 DDL 鎖相容 (不阻塞 DDL) 但被衝突 DDL 鎖中斷 | 不直接阻塞 DML 但導致游標/過程失效，下次執行需重新解析 (Hard Parse) |
SELECT * FROM emp → Shared Pool 緩存游標，獲取 emp 解析鎖ALTER TABLE emp ADD (addr VARCHAR2(100)) → 獲取排他 DDL 鎖-- 當前 DDL 鎖持有情況
SELECT session_id, name, type, mode_held, mode_requested
FROM dba_ddl_locks
WHERE name = 'EMP';
-- 解析鎖失效統計 (V$LIBRARYCACHE)
SELECT namespace, gets, gethits, pins, pinhits, reloads, invalidations
FROM v$librarycache
WHERE namespace IN ('SQL AREA','TABLE/PROCEDURE','BODY');| 場景 | 風險 | 應對策略 |
|---|---|---|
線上加欄位 ALTER TABLE big_table ADD col | 排他 DDL 鎖阻塞所有 DML，大表元資料更新耗時長 | • 12c+ ALTER TABLE ... ADD col DEFAULT val NOT NULL (僅元資料，秒級)• 維護窗口執行、 DBMS_REDEFINITION 線上重定義 |
建立視圖 CREATE VIEW v AS SELECT * FROM t | 共享 DDL 鎖阻塞 ALTER TABLE t | 避免長時間持有視圖定義會話，DDL 前檢查 DBA_DDL_LOCKS |
重新編譯套件 ALTER PACKAGE pkg COMPILE | 共享 DDL 鎖鎖定引用表，導致表 DDL 等待 | 批次編譯用 DBMS_UTILITY.COMPILE_SCHEMA，非高峰執行 |
| 高併發 OLTP 遭遇 DDL | 大量游標失效 → 硬解析風暴 → Library Cache 鎖爭用 → 數據庫凍結 | • 嚴禁業務高峰 DDL • 使用 Edition-Based Redefinition (11gR2+) 無停機變更 • 關鍵熱點 SQL 使用 DBMS_SHARED_POOL.KEEP 釘住 |
-- ddl_lock_monitor.sql (每分鐘排程)
SET SERVEROUTPUT ON
DECLARE
v_cnt NUMBER;
BEGIN
SELECT COUNT(*) INTO v_cnt
FROM dba_ddl_locks
WHERE mode_held != 'None' AND mode_requested != 'None';
IF v_cnt > 0 THEN
-- 發送告警 (郵件/釘釘/企業微信)
FOR r IN (SELECT session_id, name, type, mode_held, mode_requested
FROM dba_ddl_locks
WHERE mode_held != 'None' AND mode_requested != 'None') LOOP
DBMS_OUTPUT.PUT_LINE('DDL鎖衝突: Session=' || r.session_id ||
' Object=' || r.name || ' Type=' || r.type ||
' Held=' || r.mode_held || ' Req=' || r.mode_requested);
END LOOP;
END IF;
END;
/
-- 前置條件: @?/rdbms/admin/catblock.sql (建立 DBA_DDL_LOCKS 等視圖，需 SYS 執行)DDL 鎖不僅是「鎖」，更是「事務邊界粉碎機」與「游標失效引爆器」。DBA 必須內化：DDL 前後必須顯式提交/回滾、業務高峰嚴禁 DDL、大表結構變更走線上重定義、監控 DBA_DDL_LOCKS 與 Library Cache 失效率。將這些規範寫入變更管理流程，才能在享受 DDL 靈活性的同時，守住業務連續性的底線。
