Query that gives us a list of blocking sessions and the sessions that they are blocking:

To see what session is blocking other sessions or query that gives us a list of blocking sessions and the sessions that they are blocking:

select blocking_session, sid, serial#, wait_class, seconds_in_wait From v$session
where blocking_session is not NULL order by blocking_session;

BLOCKING_SESSION SID SERIAL# WAIT_CLASS SECONDS_IN_WAIT
---------------- ---------- ---------- -------------------- --------
148 135 61521 Idle 64

In this case, we find that session 148 is blocking session 135 and has been for 64 seconds.

We would then want to find out who is running session 148, and go find them and see why they are having a problem.

to view current Oracle users
SQL> select sid, serial#, username, osuser, machine from v$session where username is not NULL;

SID SERIAL# USERNAME OSUSER MACHINE
---------- ---------- ------------------------- ---------- -------------------
122 49671 GRUMPY grummy htmldb.com
141 45178 IMPORTANT_STUFF oracle htmldb.com

Sphere: Related Content