Friday, December 25, 2009

How to check for blocking session in oracle?

It happens many a times that a user calls you and tells you that he has executed a query a long time back which is a very small update or delete statement and the Sql screen is in a hung state. In this case it is proably the user session is being blocked by some other session. So the easiest way to track this is using the v$session view in oracle 10g. This view has a field blocking_session, which will tell you what sid is blocking your session.
e.g
suppose user joe calls and tells you the same problem, his userid is XY123
-
sql>select sid,serial#,blocking_session grom v$session where username='XY123';

output

sql> 523,1234,321

so here we can see session 321 is blocking session 523 of joe.

I have always found this useful as i come across this situation many a times.

No comments:

Post a Comment