Oracle blocking locks (sessions) query

I was working on a blocking lock issue the other day with OEM and discovered I really like the blocking sessions page. I thought it’d be nice to have that functionality in a query too, so I took to writing something similar. Here is what I came up with. Maybe later I’ll update it to do the hierarchical layout, but this works just great.

select dbs.sid holding_sid, dbs.serial# holding_serial#, dbs.username holding_user, 
       (select count(sid) from v$session where blocking_session = dbs.sid) sessions_blocked,
       dbs.sql_id holding_sql_id, dbs.wait_class holding_class, dbs.event holding_event, dbs.seconds_in_wait holding_secs, 
       dws.sid waiting_sid, dws.serial# waiting_serial#, dws.username waiting_user, dws.sql_id waiting_sql_id, dws.wait_class waiting_class, dws.event waiting_event, dws.seconds_in_wait waiting_secs
from v$session dbs, v$session dws
where dws.blocking_session = dbs.sid
order by dbs.sid, dws.seconds_in_wait desc
/

HOLDING_SID HOLDING_SERIAL# HOLDING_USER    SESSIONS_BLOCKED HOLDING_SQL_I HOLDING_CLASS        HOLDING_EVENT
----------- --------------- --------------- ---------------- ------------- -------------------- -----------------------------------
HOLDING_SECS WAITING_SID WAITING_SERIAL# WAITING_USER    WAITING_SQL_I WAITING_CLASS        WAITING_EVENT                       WAITING_SECS
------------ ----------- --------------- --------------- ------------- -------------------- ----------------------------------- ------------
       3569            4547 RDVRGW_SLTL                    3 8nwm8h2v3x9wf Idle                 SQL*Net message from client
          34        2141           35137 RDVRGW_SLTL     adxnzz2c9b7jn Application          enq: TX - row lock contention                 35

       3569            4547 RDVRGW_SLTL                    3 8nwm8h2v3x9wf Idle                 SQL*Net message from client
          34        1293           45029 RDVRGW_SLTL     adxnzz2c9b7jn Application          enq: TX - row lock contention                 27

       3569            4547 RDVRGW_SLTL                    3 8nwm8h2v3x9wf Idle                 SQL*Net message from client
          34        2153           16189 RDVRGW_SLTL     adxnzz2c9b7jn Application          enq: TX - row lock contention                 18

Here is a screenshot of OEM for comparison. I didn’t do everything the same intentionally.

blockinglocks

3 responses to “Oracle blocking locks (sessions) query

  1. Pingback: Oracle blocking locks (sessions) query | Jed's

  2. Superb

  3. Awesome !

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s