Friday, November 23, 2007

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?

4 comments:

Anonymous said...

I would like to know the sql query which gave the below output:
"Example 24–13 Finding the Text for These SQL Statements."

Also, when I try to create a Lock and try to find blocker Text, I get 0 value for both sql_hash_value and sql_address from v$session.

Xia Zhang said...

http://www.shopbestgoods.com/
http://www.bestcustomsonline.com/
http://www.nike-jordanshoes.com/
http://www.polo-tshirts.com/
http://www.burberry-factory.com/
http://www.kate-spades.com/
http://www.barbour-factory.com/
http://www.coachlosangeles.com/
http://www.official-coachoutlet.com/
http://www.louisvuittonas.com/
http://www.burberryoutlet2014.com/
http://www.official-mkoutlet.com/
http://www.official-pradaoutlet.com/
http://www.beatsbydreoutlet.net/
http://www.michaelkorsonlineusa.com/
http://www.northsclearance.com/
http://www.ralph-laurensale.com/
http://www.gucci-shoesuk2014.com/
http://www.michael-korsusa.com/
http://www.polo-outlets.com/
http://www.hermes-outletonline.com/
http://www.ralphslauren.co.uk/
http://www.marcjacobsonsale.com/
http://www.mcmworldwides.com/
http://www.warmbootssale.com/
http://www.salongchamppairs.com/
http://www.canada-gooser.com/
http://www.michaelkors.so/
http://www.oakley-sunglassoutlet.com/
http://www.north-faceoutlets.net/
http://www.moncler-clearance.com/
http://www.woolrich-clearance.com/
http://www.barbour-jacketsoutlet.com/
http://www.moncler-jacketsoutletonline.com/
http://www.monsterbeatsbydres.net/
http://www.lv-guccishoesfactory.com/
http://www.cheapdiscountoutlet.com/
http://www.coachsfactoryoutlet.com/
http://www.coach-blackfriday2014.com/
http://www.coach-storeoutletonline.com/
http://www.coach-factorysoutletonline.com/
http://www.coachccoachoutlet.com/
http://www.coach-factories.net/
http://www.coach-pursesoutletonline.com/
http://www.coach-outletsusa.com/
http://www.zxcoachoutlet.com/
http://www.mischristmas.com/
http://www.misblackfriday.com/
http://www.newoutletonlinemall.com/
http://www.ralphlaurenepolo.com/
http://michaelkorsoutlet.mischristmas.com/
http://mcmbackpack.mischristmas.com/
http://monsterbeats.mischristmas.com/
http://northfaceoutlet.mischristmas.com/
http://mk.misblackfriday.com/
http://coachoutlet.misblackfriday.com/
http://coachfactory.misblackfriday.com/
http://uggaustralia.misblackfriday.com/
http://coachpurses.misblackfriday.com/
http://coachusa.misblackfriday.com/
http://coach.misblackfriday.com/
http://michaelkorss.misblackfriday.com/
http://michaelkors.misblackfriday.com/
http://airmax.misblackfriday.com/
http://michael-kors.misblackfriday.com/
https://twitter.com/CoachOutlet2014
https://www.facebook.com/coachoutletstoreonline
https://www.facebook.com/ralphlaurenoutletonline

jeje said...

jordan retro
patriots jersey
adidas eqt
jordan 11
longchamp handbags
cheap nfl jerseys china
birkin bag
lebron 15 shoes
michael kors handbags
kate spade outlet online

chenlili said...

mbt shoes
canada goose jackets
pandora jewelry outlet
canada goose outlet online
mcm handbags
cheap jerseys wholesale
michael kors outlet online
polo ralph lauren
michael kors outlet clearance
michael kors outlet clearance
shenyuhang20180323