萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 鎖等待查詢與強制釋放腳本

鎖等待詳細查詢

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名稱典型場景
TMDML Enqueue表鎖 (ROW SHARE/EXCLUSIVE 等)
TXTransaction行鎖 (UPDATE/DELETE 未提交)
ULUser LockDBMS_LOCK 申請
STSpace Transaction空間管理

LMODE / REQUEST 解碼

  • 0: None
  • 1: Null (NULL)
  • 2: Row-S (SS) - SELECT FOR UPDATE / LOCK ROW SHARE
  • 3: Row-X (SX) - LOCK ROW EXCLUSIVE
  • 4: Share (S) - LOCK SHARE
  • 5: S/Row-X (SSX) - LOCK SHARE ROW EXCLUSIVE
  • 6: Exclusive (X) - LOCK EXCLUSIVE / DDL
copyright © 萬盛學電腦網 all rights reserved