Query tuning to find locks

Query using v$process and v$locked_object to see specific locks

SELECT s.sid, s.serial#, p.spid
FROM v$session s, v$process p WHERE s.paddr = p.addr AND
s.sid IN (SELECT SESSION_ID FROM v$locked_object);

Query to find all blocking locks in database

SELECT
    i.instance_name, l.session_id || ' / ' || s.serial#, s.status
    session_status, l.oracle_username, o.owner, o.object_name, o.object_type,                       
    DECODE (   l.locked_mode, 0, 'None', 1, 'NoLock', 2, 'Row-Share (SS)',
    3, 'Row-Exclusive (SX)', 4, 'Share-Table', 5, 'Share-Row-Exclusive (SSX)', 
    6, 'Exclusive',   
       '[Nothing]'
           )  locked_mode
FROM
    dba_objects o, gv$session s, gv$locked_object l, gv$instance i
WHERE
  i.inst_id = l.inst_id AND s.inst_id = l.inst_id
  AND s.sid = l.session_id
  AND o.object_id = l.object_id
ORDER BY
    i.instance_name, l.session_id;

Query all DML/DDL locks in the database

SELECT

    i.instance_name, l.session_id || ' / ' || s.serial#,
    s.status, s.username, l.lock_type, l.mode_held,
    o.owner || '.' || o.object_name, SUBSTR(s.program, 0, 20),
    ROUND(w.seconds_in_wait/60, 2)                 
FROM
    v$instance i, v$session s, dba_locks l, dba_objects o, v$session_wait  w
WHERE
  s.sid = l.session_id
  AND l.lock_type IN ('DML','DDL')
  AND l.lock_id1 = o.object_id
  AND l.session_id = w.sid
ORDER BY
    i.instance_name, l.session_id;

Query to Show locked objects

SELECT oracle_username || ' (' || s.osuser || ')' username, s.sid || ',' || s.serial# sess_id
,  owner || ',' || object_name object,  object_type,  DECODE( l.block,  0, 'Not Blocking'
, 1, 'Blocking',  2,  'Global') status,  DECODE(v.locked_mode,  0, 'None',  1,'Null'
, 2, 'Row-S (SS)',  3, 'Row-X (SX)',  4, 'Share',  5, 'S/Row-X (SSX)'
, 6, 'Exclusive', TO_CHAR(lmode)) mode_held
 FROM v$locked_object v, dba_objects d, v$lock l, v$session s
WHERE v.object_id = d.object_id
AND v.object_id = l.id1 AND v.session_id = s.sid
ORDER BY oracle_username,  session_id;

Query to show locks for a Specific User‎s

SELECT NVL(b.username,'SYS') username, session_id,lock_type,mode_held,
mode_requested,lock_id1,lock_id2
FROM sys.dba_lock_internal a, sys.v_$session b
WHERE username='HRMS';

DDL Locks Currently in use

SELECT   NVL (owner, 'SYS') owner, session_id, NAME, TYPE, mode_held,
         mode_requested
    FROM dba_ddl_locks
ORDER BY 2;

DML locks Currently in use

SELECT   NVL (owner, 'SYS') owner, session_id, NAME, mode_held, mode_requested
    FROM sys.dba_dml_locks
ORDER BY 2;

Query to find DML locks along with the time they have been holding the lock

SELECT
    i.instance_name, l.sid || ' / ' || s.serial#, s.username, DECODE(   l.lmode, 1, NULL, 2, 'Row Share', 3, 'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive',    'None'), DECODE(   l.request, 1, NULL, 2, 'Row Share', 3, 'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive',    'None')                           
  , DECODE (   l.type, 'CF', 'Control File', 'DX', 'Distributed Transaction', 'FS', 'File Set', 'IR', 'Instance Recovery', 'IS', 'Instance State', 'IV', 'Libcache Invalidation', 'LS', 'Log Start or Log Switch', 'MR', 'Media Recovery', 'RT', 'Redo Thread', 'RW', 'Row Wait', 'SQ', 'Sequence Number', 'ST', 'Diskspace Transaction'             , 'TE', 'Extend Table', 'TT', 'Temp Table', 'TX', 'Transaction', 'TM', 'DML', 'UL', 'PLSQL User_lock'             , 'UN', 'User Name',  'Nothing'  ) , o.owner || '.' || o.object_name , ROUND(l.ctime/60, 2)                           
FROM
    v$instance i, v$session s, v$lock l, dba_objects o, dba_tables t
WHERE
  l.id1    =  o.object_id
  AND s.sid            =  l.sid
  AND o.owner          =  t.owner
  AND o.object_name    =  t.table_name
  AND o.owner          <> 'SYS'
  AND l.type           =  'TM'
ORDER BY
    i.instance_name, l.sid;

Locks held by all users

SELECT s.username, s.sid, DECODE ( l.TYPE,
          'MR', 'Media Recovery', 'RT', 'Redo Thread',  'UN', 'User Name', 'TX', 'Transaction',
          'TM', 'DML', 'UL', 'PL/SQL User Lock', 'DX', 'Distributed Xaction', 'CF', 'Control File',
          'IS', 'Instance State', 'DS', 'File Set', 'IR', 'Instance Recovery', 'ST', 'Disk Space Transaction',
          'TS', 'Temp Segment', 'IV', 'Library Cache Invalidation', 'LS', 'Log Start or Switch',
          'RW', 'Row Wait','SQ', 'Sequence Number', 'TE', 'Extend Table', 'TT', 'Temp Table'
       ) ltype, o.object_name,
       DECODE (  l.lmode, 2, 'Row-S(SS)', 3, 'Row-X(SX)', 4, 'Share', 5, 'S/Row-X(SSX)', 6, 'Exclusive',
          'Other'   ) mode_held
  FROM dba_objects o, v$session s, v$lock l
 WHERE s.sid = l.sid AND o.object_id = l.id1;

Query to find Deadlock

SELECT sn.username, m.sid, m.type,
   DECODE(m.lmode, 0, 'None'
                 , 1, 'Null'
                 , 2, 'Row Share'
                 , 3, 'Row Excl.'
                 , 4, 'Share'
                 , 5, 'S/Row Excl.'
                 , 6, 'Exclusive'
                 , lmode, ltrim(to_char(lmode,'990'))) lmode,
   DECODE(m.request, 0, 'None'
                 , 1, 'Null'
                 , 2, 'Row Share'
                 , 3, 'Row Excl.'
                 , 4, 'Share'
                 , 5, 'S/Row Excl.'
                 , 6, 'Exclusive'
                 , request, ltrim(to_char(request,'990'))) request,
         m.id1,m.id2
FROM v$session sn, V$lock m
WHERE (sn.sid = m.sid AND m.request != 0)
   OR (sn.sid = m.sid
      AND m.request = 0 AND lmode != 4
      AND (id1, id2 ) IN (SELECT s.id1, s.id2
                          FROM v$lock s
                          WHERE request != 0
                                 AND s.id1 = m.id1
                                 AND s.id2 = m.id2 )
      )
ORDER BY id1,id2, m.request;