Skip to Main Content
  • Questions

  • How to get SQL text for session blocked and which session blocked it

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Chris Saxon

Thanks for the question, Sujit.

Asked: March 02, 2017 - 1:43 pm UTC

Last updated: March 03, 2017 - 7:32 am UTC

Version: Oracle 12c

Viewed 1000+ times

You Asked

I tried finding out ways to get SQL text for blocked and blocker session's sql. I am not able to get to it. V$SESSION contains either currently executing SQL ID or previous one only. But the session for which the other session is blocked may issue multiple SQL calls after the blocking sql execution. Is there any way to get both of the sql text?

I tried below example

create table D (x int)

insert into D values (1)
insert into D values (2)

Session-1 -- I ran below update query
update D set X=10 where X=2;

Session-2 -- I ran below same update query
update D set X=15 where X=2;

It gets blocked.

No commits are made.

and Connor said...

You cant really get the SQL text that *caused* the block because, unlike the person that is *currently* blocked, there is no guarantee that the session that locked the rows is running anything.

They might have run 'update D set X=10 where X=2' two days ago, and have been sitting there idle ever since. Also, there is no "one" SQL that might have caused the problem. That session might have done:

update D set X=10 where X=2;

then

update D set X=15

then

update D set X=1
where not exists ( select null from emp where empno = d.x )

all of which will update/lock the row where x=2

v$session will tell you the blocking_session_id, which gives you information to either kill the session (or perhaps phone the person doing it!). Also, you can use resource manager to automatically kill a session that is idle but is blocking others.

https://docs.oracle.com/cd/E11882_01/server.112/e25494/dbrm.htm#ADMIN027


Rating

  (1 rating)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

Thanks Connor for your quick response

Sujit, March 03, 2017 - 9:52 am UTC

Thanks Connor for your quick response
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.