SQL Query to identify Oracle Database Locks

The following query will list database locks, but needs to be run as a user with the appropriate access to the v$… and dba_locks tables:

SELECT w.session_id   waiting_session
,      h.session_id   holding_session
,      w.lock_type
--,      h.mode_held
--,      w.mode_requested
,      (
       SELECT module 
       ||     ' :: ' 
       ||     action 
       FROM   v$session 
       WHERE  sid=h.session_id
       )              holding_module_action
,      (
       SELECT module 
       ||     ' :: ' 
       ||     action 
       FROM   v$session 
       WHERE  sid = w.session_id
       )              waiting_module_action
FROM   (
       SELECT /*+ NO_MERGE */ * 
       FROM   dba_locks
       )              w
,      (
       SELECT /*+ NO_MERGE */ * 
       FROM   dba_locks
       )              h
WHERE  ((   (h.mode_held != 'None') 
        AND (h.mode_held != 'Null')
        AND (  (h.mode_requested = 'None') 
            OR (h.mode_requested = 'Null')
            )
        )
        AND ((  (w.mode_held = 'None') 
             OR (w.mode_held = 'Null')
             )
             AND (   (w.mode_requested != 'None') 
                 AND (w.mode_requested != 'Null')
                 )
             )
       )
AND    w.lock_type = h.lock_type
AND    w.lock_id1 = h.lock_id1
AND    w.lock_id2 = h.lock_id2
AND    w.session_id != h.session_id
;

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s