註釋：

該SQL可查詢多會話,非select的DML操作,同時操作A表引起的鎖 ..會話之間的關係~

....下文有該SQL用到的檢視/欄位的詳細註釋..

....若想顯示其他欄位可以按照自己需求增加 ..

SQL:

SELECT DISTINCT S.SID , /*會話的唯一標識，通常要對某個會話進行分析前，首先就需要獲得該會話的SID。*/

--S.SERIAL# /*會話的序號*/,

S.STATE /*WAIT STATE~*/ ,

S.BLOCKING_SESSION ,

--SESSION IDENTIFIER OF THE BLOCKING SESSION. THIS COLUMN IS VALID ONLY IF BLOCKING_SESSION_STATUS HAS THE VALUE VALID.

S.BLOCKING_SESSION_STATUS STATUS, /*THIS COLUMN PROVIDES DETAILS ON WHETHER THERE IS A BLOCKING SESSION: */

( CASE

WHEN SQL_TEXT IS NULL /*LO.REQUEST = 0 */

THEN

'(SID:' || S.SID || ')會話 SQL已跑完'

ELSE

'(SID:' || S.SID || ')會話正執行SQL：' || SQL_.SQL_TEXT

END ) SQL_TEXT /*執行完的SQL'SQL_TEXT標記SQL已跑完，否則標記SQL'*/ ,

--SQL_.SQL_FULLTEXT SQL全文字,

S.USERNAME /*建立該會話的使用者名稱*/ ,

O.OWNER || '.' || O.OBJECT_NAME 鎖的物件, --V$SESSION.ROW_WAIT_OBJ#若操作完的該欄位值=-1，所以關聯的V$LOCKED_OBJECT取鎖表

LO.REQUEST , -- Lock mode in which the process requests the lock 會話申請的鎖的模式

S.EVENT ,

S.MACHINE /*客戶端的機器名。*/ ,

S.LOGON_TIME /*登陸時間*/ ,

'ALTER SYSTEM KILL SESSION ''' || S.SID || ',' || S.SERIAL# || ''';' KILL --若存在鎖情況,會用到KILL鎖釋放~

FROM V$SESSION S

LEFT JOIN V$SQL SQL_

ON SQL_.SQL_ID = S.SQL_ID

JOIN V$LOCKED_OBJECT L

ON L.SESSION_ID = S.SID

JOIN ALL_OBJECTS O

ON L.OBJECT_ID = O.OBJECT_ID

JOIN V$LOCK LO

ON (LO.BLOCK != 0 OR LO.REQUEST != 0 )

--V$LOCK.block => A value of either 0 or 1, depending on whether or not the lock in question is the blocker

--V$LOCK.REQUEST => Lock mode in which the process requests the lock:下文有值的意義~ ['0 - none']

WHERE LO.SID = L.SESSION_ID

AND LO.SID = S.SID

ORDER BY S.BLOCKING_SESSION DESC ;

註釋：

--檢視==官網註釋

--v$session        ==

--V$SQL            ==

--V$LOCK           ==

--V$LOCKED_OBJECT ==

--ALL_OBJECTS      ==

--顯示欄位==官網註釋：

V$SESSION.STATE = Wait state :

--WAITING - Session is currently waiting

--WAITED UNKNOWN TIME - Duration of the last wait is unknown; this is the value when the parameter TIMED_STATISTICS is set to false

--WAITED SHORT TIME - Last wait was less than a hundredth of a second

--WAITED KNOWN TIME - Duration of the last wait is specified in the WAIT_TIME column S.BLOCKING_SESSION ,

--Session identifier of the blocking session. This column is valid only if BLOCKING_SESSION_STATUS has the value VALID.

V$SESSION.BLOCKING_SESSION_STATUS = This column provides details on whether there is a blocking session :

--VALID - there is a blocking session, and it is identified in the BLOCKING_INSTANCE and BLOCKING_SESSION columns

--NO HOLDER - there is no session blocking this session

--NOT IN WAIT - this session is not in a wait

--UNKNOWN - the blocking session is unknown

V$LOCK.REQUEST = Lock mode in which the process requests the lock :

--0 - none

--1 - null (NULL)

--2 - row-S (SS)

--3 - row-X (SX)

--4 - share (S)

--5 - S/Row-X (SSX)

--6 - exclusive (X)

祝好~

Oracle 查詢鎖之間的依賴關係

相關文章