Lock

Example 24–11 Finding the Sessions Holding the Lock

Find the (ID1, ID2, type) for sessions waiting for a lock (LMODE=0).

Find the session holding the lock (REQUEST=0) for that ID1, ID2, type.

SELECT lpad(' ',DECODE(request,0,0,1))||sid sess, id1, id2, lmode, request, type
FROM V$LOCK
WHERE id1 IN (SELECT id1 FROM V$LOCK WHERE lmode = 0)
ORDER BY id1,request

SID ID1 ID2 LMODE REQUEST TY
------ ---------- ---------- ---------- ---------- --
1237 196705 200493 6 0 TX <- Lock Holder
1256 196705 200493 0 6 TX <- Lock Waiter
1176 196705 200493 0 6 TX <- Lock Waiter
938 589854 201352 6 0 TX <- Lock Holder
1634 589854 201352 0 6 TX <- Lock Waiter

Example 24–12 Finding the Statements being Executed by These Sessions

SELECT sid, sql_hash_value
FROM V$SESSION
WHERE SID IN (1237,1256,1176,938,1634);

SID SQL_HASH_VALUE
----- --------------
938 2078523611 <-Holder
1176 1646972797 <-Waiter
1237 3735785744 <-Holder
1256 1141994875 <-Waiter
1634 2417993520 <-Waiter


Example 24–13 Finding the Text for These SQL Statements

HASH_VALUE SQL_TEXT
---------- ----------------------------------------------------------------
1141994875
SELECT TO_CHAR(CURRENT_MAX_UNIQUE_IDENTIFIER + 1 ) FROM PO_UNI
QUE_IDENTIFIER_CONTROL WHERE TABLE_NAME = DECODE(:b1,'RFQ','PO_
HEADERS_RFQ','QUOTATION','PO_HEADERS_QUOTE','PO_HEADERS') FOR UP
DATE OF CURRENT_MAX_UNIQUE_IDENTIFIER

1646972797
SELECT TO_CHAR(CURRENT_MAX_UNIQUE_IDENTIFIER + 1 ) FROM PO_UNI
QUE_IDENTIFIER_CONTROL WHERE TABLE_NAME = 'PO_HEADERS' FOR UPD
ATE OF CURRENT_MAX_UNIQUE_IDENTIFIER

2078523611
select CODE_COMBINATION_ID, enabled_flag, nvl(to_char(start_da
te_active, 'J'), -1), nvl(to_char(end_date_active, 'J'), -1), S
EGMENT2||'.'||SEGMENT1||'.'||||SEGMENT6,detail_posting_allowed_f
lag,summary_flag from GL_CODE_COMBINATIONS where CHART_OF_ACCO
UNTS_ID = 101 and SEGMENT2 in ('000','341','367','388','389','4
52','476','593','729','N38','N40','Q21','Q31','U21') order by S
EGMENT2, SEGMENT1, SEGMENT6

2417993520
select 0 into :b0 from pa_projects where project_id=:b1 for upd
ate

3735785744
begin :X0 := FND_ATTACHMENT_UTIL_PKG.GET_ATCHMT_EXISTS(:L_ENTITY
_NAME, :L_PKEY1, :L_PKEY2, :L_PKEY3, :L_PKEY4, :L_PKEY5, :L_FUNC
TION_NAME, :L_FUNCTION_TYPE); end;

The locked sessions’ statements show that the sessions 1176 and 1256 are waiting
for a lock on the PO_UNIQUE_IDENTIFIER_CONTROL held by session 1237, while
session 1634 is waiting for a lock on PA_PROJECTS held by session 938. Query
V$SESSION_WAIT, V$SESSION, and V$SESSION_EVENT to get more details about
the sessions and users. For example:

n Who is holding the lock?

n Is the session holding the lock active or idle

n Is the session executing long running queries while holding the lock?