Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Saturday, 12 January 2013

Query 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;

1 comments:

  1. Did you know that that you can make cash by locking special pages of your blog / site?
    Simply open an account on Mgcash and run their content locking widget.

    ReplyDelete