SELECT SUBSTR(v$lock.sid,1,4) "SID",
SUBSTR(username,1,12) "UserName",
SUBSTR(object_name,1,25) "ObjectName",
v$lock.type "LockType",
DECODE(RTRIM(SUBSTR(lmode,1,4)),
'2','Row-S (SS)','3','Row-X (SX)',
'4','Share','5','S/Row-X (SSX)',
'6','Exclusive','Other') "LockMode",
SUBSTR(v$session.program,1,25) "ProgramName"
FROM V$LOCK, SYS.DBA_OBJECTS, V$SESSION
WHERE OBJECT_ID = v$lock.id1
AND v$lock.sid = v$session.sid
AND username IS NOT NULL
AND username NOT IN ('SYS','SYSTEM')
AND SERIAL# != 1;
SELECT a.sid, a.serial#, b.spid, a.status, a.program
FROM v$session a, v$process b
WHERE a.paddr = b.addr
ORDER BY a.sid;
ALTER SYSTEM KILL SESSION '200,68' IMMEDIATE;
其中 200 為 SID,68 為 SERIAL#,從上述查詢獲得。
| Type | 名稱 | 典型場景 |
|---|---|---|
| TM | DML Enqueue | 表鎖 (ROW SHARE/EXCLUSIVE 等) |
| TX | Transaction | 行鎖 (UPDATE/DELETE 未提交) |
| UL | User Lock | DBMS_LOCK 申請 |
| ST | Space Transaction | 空間管理 |