Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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
    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',   
           )  locked_mode
    dba_objects o, gv$session s, gv$locked_object l, gv$instance i
  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
    i.instance_name, l.session_id;
Query all DML/DDL locks in the database

    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)                 
    v$instance i, v$session s, dba_locks l, dba_objects o, v$session_wait  w
  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
    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,
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,
    FROM dba_ddl_locks
DML locks Currently in use
SELECT   NVL (owner, 'SYS') owner, session_id, NAME, mode_held, mode_requested
    FROM sys.dba_dml_locks
Query to find DML locks along with the time they have been holding the lock
    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)                           
    v$instance i, v$session s, v$lock l, dba_objects o, dba_tables t
  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'
    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,
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;


Popular posts from this blog

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

PostgreSQL Database startup / shutdown /restart

PostgreSQL Pgbadger Installation On Linux

ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated ORA-01262: Stat failed on a file destination directory Linux-x86_64 Error: 2: No such file or directory

PostgreSQL pgBadger