Oracle 資料庫常見鎖定問題排查與解鎖實戰

Oracle 資料庫常見鎖定問題排查與解鎖實戰

提交事務時遇到「資料庫被使用者鎖定」錯誤，是 DBA 與開發人員常面臨的場景。本文提供標準排查流程：從鎖定物件查詢、會話關聯分析，到安全解鎖的完整步驟與 SQL 範本。

1. 查詢被鎖定的物件與會話

-- 步驟 1：找出被鎖定的物件及會話 ID
SELECT object_id, session_id, locked_mode
FROM   v$locked_object;

-- 步驟 2：關聯 v$session 取得使用者、登入時間等詳細資訊
SELECT t2.username,
       t2.sid,
       t2.serial#,
       t2.logon_time
FROM   v$locked_object t1
JOIN   v$session t2 ON t1.session_id = t2.sid
ORDER BY t2.logon_time;

2. 強制終止阻塞會話 (解鎖)

確認目標會話的 SID 與 SERIAL# 後，執行：

ALTER SYSTEM KILL SESSION 'sid,serial#';

範例：

ALTER SYSTEM KILL SESSION '7,10839';

3. 進階排查建議

• 識別鎖定類型：LOCKED_MODE 值對應 (3=Row-X, 6=Exclusive) 判斷鎖定嚴重度。
• 檢查 SQL 文本：結合 V$SQL 或 V$SQLAREA 找出執行中的陳述式。
• 應用層面優化：縮短事務長度、避免長時間未提交、合理設計索引減少鎖衝突。
• 監控工具：善用 Enterprise Manager、ASH 報表或自訂監控腳本持續追蹤鎖定事件。

4. 注意事項

• 殺掉會話會導致該事務回滾，可能影響業務邏輯，請在低峰期或經確認後操作。
• 若頻繁發生鎖定，建議從應用程式碼層面根治 (如調整隔離級別、樂觀鎖機制)。

掌握上述查詢與處理流程，可快速定位並解決 Oracle 鎖定導致的業務阻塞問題。