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
;

..or you can try the following:

SELECT NVL(s.username, '(oracle)') AS username
,      s.sid
,      s.serial#
,      s.osuser
,      s.machine
,      s.program
,      s.module
,      o.object_name
,      sq.sql_text
,      TO_CHAR(s.logon_time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM   v$locked_object l
,      dba_objects     o
,      v$session       s
,      v$process       p
,      v$sql sq
WHERE  l.object_id = o.object_id
AND    l.session_id = s.sid
AND    s.paddr = p.addr
AND    s.sql_address = sq.address
;

Locking sessions can be killed using the following SQL command, using the SID and SERIAL# from the query above:

ALTER SYSTEM KILL SESSION '<SID>,<SERIAL#>' IMMEDIATE;
Advertisements

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