SQLPLUS / AS SYSDBA
@?/rdbms/admin/catblock.sql
-- 建立 DBA_WAITERS、DBA_BLOCKERS 視圖及同義詞
SELECT * FROM test FOR UPDATE;
SELECT * FROM test FOR UPDATE; -- 阻塞
SELECT * FROM dba_waiters;
WAITING_SESSION HOLDING_SESSION LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1 LOCK_ID2
--------------- --------------- ------------------------- ---------------------- ----------------------- -------------------- --------------- ---------
142 150 Transaction Exclusive Exclusive 131099 2550
SELECT * FROM dba_blockers;
HOLDING_SESSION
---------------
150
清晰顯示:Session 150 持有排他鎖,Session 142 等待。
SELECT * FROM obj$ FOR UPDATE;
CREATE INDEX ind_t_id ON test(object_id); -- 阻塞於 DDL 鎖
SELECT * FROM dba_waiters;
WAITING_SESSION HOLDING_SESSION LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1 LOCK_ID2
--------------- --------------- -------------------------- ---------------------------------------- ---------------------------------------- ---------- ----------
150 145 Transaction Exclusive Exclusive 1310729 1391
SELECT * FROM dba_blockers;
HOLDING_SESSION
---------------
145
| 欄位 | 含義 |
|---|---|
| WAITING_SESSION | 等待者 SID |
| HOLDING_SESSION | 持有者 SID |
| LOCK_TYPE | 鎖類型:Transaction/DML/DDL 等 |
| MODE_HELD | 持有模式 |
| MODE_REQUESTED | 請求模式 |
| LOCK_ID1/ID2 | 鎖標識 (ID1=Object ID/Transaction ID, ID2=Slot/Block) |
SELECT w.waiting_session, ws.username wait_user, ws.sql_id wait_sql,
w.holding_session, hs.username hold_user, hs.sql_id hold_sql,
w.lock_type, w.mode_held, w.mode_requested
FROM dba_waiters w
JOIN v$session ws ON w.waiting_session = ws.sid
JOIN v$session hs ON w.holding_session = hs.sid;
可直接看到雙方執行的 SQL、用戶、機器等資訊,快速定位問題根因。