Dependency between Oracle query locks

Dependency between Oracle query locks

Note:

This SQL statement can be used to query multi-session, non-select DML operations, and operate the locks caused by Table A... the relationship between sessions ~

... Detailed comments of views/fields used by the SQL statement are provided below ..

... If you want to display other fields, you can add them as needed ..

SQL:

Select distinct s. SID,/* unique identifier of a session. Before analyzing a session, you must obtain the SID of the session. */

-- S. SERIAL #/* session Sequence Number */,

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 | ') The session SQL has been run'

ELSE

'(SID:' | S. SID | ') The session is executing SQL:' | SQL _. SQL _TEXT

END) SQL _TEXT/* The executed SQL 'SQL _ TEXT Mark SQL has been run, otherwise the SQL '*/,

-- SQL _. SQL _FULLTEXT SQL full text,

S. USERNAME/* User Name for creating the session */,

O. OWNER | '. '| O. OBJECT_NAME Lock Object, -- V $ SESSION. ROW_WAIT_OBJ # If the field value is-1 after the operation, the associated V $ LOCKED_OBJECT locks the table.

LO. REQUEST, -- Lock mode in which the process requests the lock session applied Lock mode

S. EVENT,

S. MACHINE/* client MACHINE name. */,

S. LOGON_TIME/* logon time */,

'Alter system kill session ''' | S. SID | ',' | S. SERIAL # | '''; 'Kill -- if a lock exists, the KILL lock will be used to release ~

From v $ SESSION S

Left join v $ 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: the meaning of the value below ~ ['0-none']

Where lo. SID = L. SESSION_ID

And lo. SID = S. SID

Order by s. BLOCKING_SESSION DESC;
 

Note:

-- View = official website comment

 

-- V $ session = http://docs.Oracle.com/cd/E11882_01/server.112/e40402/dynviews_3016.htm#REFRN30223

-- V $ SQL = http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_3043.htm#REFRN30246

-- V $ LOCK = http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_2027.htm#REFRN30121

-- V $ LOCKED_OBJECT = http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_2030.htm#REFRN30125

-- ALL_OBJECTS = http://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_1158.htm#REFRN20146

 

 

-- Display field = official website comment:

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)