Note: This SQL statement can be used to query multiple sessions, non-select DML operations, and operate the locks caused by Table A... relationships between sessions ~.... The following describes the View fields used by the SQL statement.
Note: This SQL statement can be used to query multi-session, non-select DML operations, and operate the locks caused by Table A. Relationships between sessions ~ ... The following describes the view/field details used by the SQL statement.
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 ==# REFRN30223
-- V $ SQL ==# REFRN30246
-- V $ LOCK ==# REFRN30121
-- V $ LOCKED_OBJECT ==# REFRN30125
-- ALL_OBJECTS ==# 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)