-- 統計被鎖物件數
SELECT COUNT(*) FROM V$LOCKED_OBJECT;
-- 列出所有鎖記錄
SELECT * FROM V$LOCKED_OBJECT;SELECT
b.OWNER,
b.OBJECT_NAME,
a.SESSION_ID,
a.LOCKED_MODE
FROM V$LOCKED_OBJECT a
JOIN DBA_OBJECTS b ON b.OBJECT_ID = a.OBJECT_ID;
-- LOCKED_MODE 解碼：
-- 0: None, 1: Null (NULL), 2: Row-S (SS), 3: Row-X (SX),
-- 4: Share (S), 5: S/Row-X (SSX), 6: Exclusive (X)
-- 常見：3=Row-X (DML 鎖), 6=Exclusive (DDL 鎖)SELECT
b.USERNAME,
b.SID,
b.SERIAL#,
b.LOGON_TIME,
b.MACHINE,
b.PROGRAM,
b.MODULE,
b.ACTION,
b.SQL_ID
FROM V$LOCKED_OBJECT a
JOIN V$SESSION b ON b.SID = a.SESSION_ID
ORDER BY b.LOGON_TIME;-- 直接查看被阻塞會話及阻塞者
SELECT
s.SID,
s.USERNAME,
s.EVENT,
s.BLOCKING_SESSION, -- 阻塞者 SID
s.SECONDS_IN_WAIT,
s.WAIT_TIME
FROM V$SESSION s
WHERE s.STATE = 'WAITING'
AND s.WAIT_CLASS != 'Idle';-- 語法：SID, SERIAL# 從上述查詢獲得
ALTER SYSTEM KILL SESSION '1025,41';
-- 11g+ 立即終止（不等待回滾）
ALTER SYSTEM KILL SESSION '1025,41' IMMEDIATE;
-- PDB 環境（12c+）
ALTER SYSTEM KILL SESSION '1025,41' CONTAINER=CURRENT;KILL SESSION 原理：標記 Session 為「待終止」，PMON 背景進程清理資源、回滾事務、釋放鎖。**非立即生效**，長事務回滾需時間。
IMMEDIATE讓 PMON 立即強制清理，但可能留下一致性風險（極少見）。
SET LINES 200 PAGES 1000
COL owner FOR A20
COL object_name FOR A30
COL username FOR A20
COL machine FOR A30
COL program FOR A40
COL blocker_sid FOR A12
-- 主查詢：被鎖表 + 持有鎖 Session + 等待鏈
SELECT
lo.OWNER,
lo.OBJECT_NAME,
lo.SESSION_ID AS holder_sid,
lo.LOCKED_MODE,
s.USERNAME AS holder_user,
s.SID,
s.SERIAL#,
s.MACHINE,
s.PROGRAM,
s.LOGON_TIME,
sw.EVENT AS holder_wait_event,
sw.SECONDS_IN_WAIT AS holder_wait_sec,
bs.SID AS blocker_sid,
bs.USERNAME AS blocker_user,
bs.SQL_ID AS blocker_sql_id,
'ALTER SYSTEM KILL SESSION ''' || s.SID || ',' || s.SERIAL# || ''';' AS kill_cmd
FROM V$LOCKED_OBJECT lo
JOIN DBA_OBJECTS o ON o.OBJECT_ID = lo.OBJECT_ID
JOIN V$SESSION s ON s.SID = lo.SESSION_ID
LEFT JOIN V$SESSION_WAIT sw ON sw.SID = s.SID
LEFT JOIN V$SESSION bs ON bs.SID = s.BLOCKING_SESSION
WHERE o.OWNER != 'SYS'
ORDER BY lo.OWNER, lo.OBJECT_NAME, s.LOGON_TIME;| 數值 | 模式名稱 | 縮寫 | 典型操作 | 相容性 |
|---|---|---|---|---|
| 0 | None | — | 無 | — |
| 1 | Null | NL | SELECT（無 FOR UPDATE） | 全相容 |
| 2 | Row Share | RS / SS | SELECT ... FOR UPDATE, LOCK TABLE ... IN ROW SHARE MODE | 與 RS, RX, S 相容 |
| 3 | Row Exclusive | RX / SX | INSERT/UPDATE/DELETE, LOCK TABLE ... IN ROW EXCLUSIVE MODE | 與 RS, RX 相容 |
| 4 | Share | S | CREATE INDEX, LOCK TABLE ... IN SHARE MODE | 與 RS, S 相容 |
| 5 | Share Row Exclusive | SRX / SSX | LOCK TABLE ... IN SHARE ROW EXCLUSIVE MODE | 僅與 RS 相容 |
| 6 | Exclusive | X | DROP/TRUNCATE/ALTER TABLE, LOCK TABLE ... IN EXCLUSIVE MODE | 全不相容 |
| 場景 | 特徵 | 建議處理 |
|---|---|---|
| 長事務未提交 | LOCKED_MODE=3/6，SECONDS_IN_WAIT 大 | 聯繫開發確認業務邏輯，決定 KILL 或等待提交 |
| 未提交的 DDL | LOCKED_MODE=6 (Exclusive) | 極度危險，**嚴禁生產直接 KILL**，需評估回滾影響 |
| 索引重建/統計收集 | 短暫 X 鎖 | 通常秒級自動釋放，無需干預 |
| 死鎖 | ORA-00060，雙方互相等待 | Oracle 自動檢測犧牲者回滾，檢查 Alert Log 定位 SQL 優化 |
| BITMAP 索引鎖升級 | 大量 DML 導致表級鎖 | OLTP 環境**禁用 BITMAP 索引**，改用 B-Tree |
PROFILE 限制 IDLE_TIME、CONNECT_TIME，配合 DRM MAX_IDLE_TIMESECONDS_IN_WAIT > 300 且 BLOCKING_SESSION IS NOT NULL 發送告警鎖表排查核心路徑：**V$LOCKED_OBJECT → DBA_OBJECTS（哪張表）→ V$SESSION（誰鎖的）→ V$SESSION.BLOCKING_SESSION（誰被誰阻）**。掌握 LOCKED_MODE 含義與 ALTER SYSTEM KILL SESSION 'sid,serial#' [IMMEDIATE] 語法，建立「一鍵輸出阻塞鏈+殺腳本」標準腳本，是 DBA 應對生產阻塞事件的必備基本功。
