快捷導(dǎo)航

oracle數(shù)據(jù)庫查看鎖表的sql語句整理

更新時(shí)間：2023年10月25日 10:36:46 作者：萬山寒

Oracle數(shù)據(jù)庫操作中,我們有時(shí)會(huì)用到鎖表查詢以及解鎖和kill進(jìn)程等操作,這篇文章主要給大家介紹了關(guān)于oracle數(shù)據(jù)庫查看鎖表的sql語句的相關(guān)資料,文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下

關(guān)鍵表

V$LOCKED_OBJECT ：記錄鎖信息的表
v$session ：記錄會(huì)話信息的表
v$sql：記錄執(zhí)行sql的表
dba_objects：用來管理對(duì)象（表、庫等等對(duì)象）

查看鎖表的SID

sql1

SELECT USERNAME,SID, LOCKWAIT, STATUS, MACHINE, PROGRAM
FROM v$session
WHERE sid IN (SELECT session_id FROM v$locked_object);

sql2

SELECT sess.SID, sess.SERIAL#, lo.ORACLE_USERNAME, lo.OS_USER_NAME, lo.LOCKED_MODE, ao.OBJECT_NAME
FROM V$LOCKED_OBJECT lo,
     v$session sess,
     dba_objects ao
WHERE lo.SESSION_ID = sess.SID
  AND ao.OBJECT_ID = lo.OBJECT_ID;

USERNAME：死鎖語句所用的數(shù)據(jù)庫用戶；
SID: session_id
LOCKWAIT：死鎖的狀態(tài)，如果有內(nèi)容表示被死鎖。
STATUS：狀態(tài)，active表示被死鎖
MACHINE：死鎖語句所在的機(jī)器。
PROGRAM：產(chǎn)生死鎖的語句主要來自哪個(gè)應(yīng)用程序。

查看鎖表執(zhí)行語句

用sid查詢單個(gè)

SELECT sql_text
FROM v$sql
WHERE hash_value IN
      (SELECT sql_hash_value FROM v$session WHERE sid='XXXXXXXX');

查詢所有加鎖的sql

SELECT sql_text
FROM v$sql
WHERE hash_value IN
      (SELECT sql_hash_value FROM v$session WHERE sid IN (SELECT session_id FROM v$locked_object));

查詢未提交事務(wù)的SQL

造成死鎖的sql事務(wù)都必定是未提交的

SELECT s.SID,
       s.USERNAME,
       s.OSUSER,
       s.PROGRAM,
       TO_CHAR(s.LOGON_TIME, 'yyyy-mm-dd hh24:mi:ss')                                             AS LOGON_TIME,
       TO_CHAR(t.START_DATE, 'yyyy-mm-dd hh24:mi:ss')                                             AS START_DATE,
       s.STATUS,
       (SELECT q.SQL_TEXT FROM v$sql q WHERE q.LAST_ACTIVE_TIME = t.START_DATE AND rownum <= 1) AS SQL_TEXT
FROM v$session s,
     v$transaction t
WHERE s.SADDR = t.SES_ADDR;

殺死鎖

ALTER SYSTEM KILL SESSION 'sess.sid, sess.serial#';

附：數(shù)字越大鎖級(jí)別越高, 影響的操作越多

1級(jí)鎖有：Select，有時(shí)會(huì)在v$locked_object出現(xiàn)。

2級(jí)鎖有：Select for update,Lock For Update,Lock Row Share
select for update當(dāng)對(duì)話使用for update子串打開一個(gè)游標(biāo)時(shí)，所有返回集中的數(shù)據(jù)行都將處于行級(jí)(Row-X)獨(dú)占式鎖定，其他對(duì)象只能查詢這些數(shù)據(jù)行，不能進(jìn)行update、delete或select for update操作。

3級(jí)鎖有：Insert, Update, Delete, Lock Row Exclusive

沒有commit之前插入同樣的一條記錄會(huì)沒有反應(yīng), 因?yàn)楹笠粋€(gè)3的鎖會(huì)一直等待上一個(gè)3的鎖, 我們必須釋放掉上一個(gè)才能繼續(xù)工作。

4級(jí)鎖有：Create Index, Lock Share

locked_mode為2,3,4不影響DML(insert,delete,update,select)操作, 但DDL(alter,drop等)操作會(huì)提示ora-00054錯(cuò)誤。