Como ver sesiones bloqueadas de usuarios.

Blocking sessions are a problem for the DBA and we need a way to find them so we can deal with them. Blocking sessions occur when a session issues an insert, update or delete command that changes a row.

When the change occurs, the row is locked until the session either commits the change, rolls the change back or the user logs off the system. You can see where problems might occur, for example a user might make a change and then forget to commit it and leaves for the weekend without logging off the system.

We can use this query to find these nasty blocking sessions. We use our old friend, v$session to find the blocking session, and also a list of sessions locked by that session.

Here is a 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.