Database Tuning (MOSC)

MOSC Banner

using the blocking session number to find out the sql and user

edited Jul 13, 2010 7:32AM in Database Tuning (MOSC) 4 commentsAnswered
 The user complained that one of the session is running slow or hang. I issue the following query:

select blocking_session || ' is blocking ' || sid || ' (' || username || ') and the wait event is ' || event "BLOCK_MESSAGE"
from v$session
where blocking_session is not null;

Sure enough there is a blocking session out there block the sid

BLOCK_MESSAGE
--------------------------------------------------------------------------------
1072 is blocking 962 (username) and the wait event is enq: TX - row lock contention

I looked at v$sql and couldn't find a common field that I can join the two views. How can I use the blocking_session number 1072 to find out the sql statement and the user who is making the lock?

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center