Edi Yanto (何 萬 新)

How to Find and Kill Locking Session in Oracle Database

Posted on: September 17, 2014


We can find the blocking and blocked sessions with the below scripts.

SELECT vs.blocking_session, vs.SID, vs.serial#, vs.seconds_in_wait
FROM v$session vs
WHERE vs.blocking_session IS NOT NULL

OR

SELECT vs.username locking_usename, vs.osuser locking_osuser, vh.SID locking_sid, vs.status status, vs.module module, vs.program program_holding,
vsw.username waiter_username, vsw.osuser waiter_osuser, vw.SID waiter_sid, vsw.program program_waiting,
‘alter system kill session ‘ || ”” || vh.SID || ‘,’ || vs.serial# || ”’;’ “Kill_Command_4_Blocking_Locks”,
‘alter system kill session ‘ || ”” || vsw.SID || ‘,’ || vsw.serial# || ”’;’ “Kill_Command_4_Blocked_Locks”
FROM v$lock vh, v$lock vw, v$session vs, v$session vsw
WHERE (vh.id1, vh.id2) IN (SELECT id1, id2
FROM v$lock
WHERE request = 0
INTERSECT
SELECT id1, id2
FROM v$lock
WHERE lmode = 0)
AND vh.id1 = vw.id1
AND vh.id2 = vw.id2
AND vh.request = 0
AND vw.lmode = 0
AND vh.SID = vs.SID
AND vw.SID = vsw.SID

Once the locking sessions have been identified, we can kill such sessions using kill command scripts below for blocking or blocked sessions.

blocked_session

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,368 other followers


Edi Yanto
Oracle Applications Technical Consultant
View Edi Yanto's profile on LinkedIn

My Certifications






Follow me on Twitter

Archives

September 2014
M T W T F S S
« May   Dec »
1234567
891011121314
15161718192021
22232425262728
2930  

Visitors

Categories

Blog Stats

  • 140,585 hits

Users Online

web counter

Pages

%d bloggers like this: