Identify locks

------/*  identify locks */-----

select owner||'.'||object_name obj   ,oracle_username||' ('||s.status||')' oruser   ,os_user_name osuser   ,machine computer   ,

l.process unix   ,s.sid||','||s.serial# ss   ,r.name rs   ,to_char(s.logon_time,'yyyy/mm/dd hh24:mi:ss') time

from v$locked_object l ,dba_objects o ,v$session s ,v$transaction t ,v$rollname r

where l.object_id = o.object_id  and s.sid=l.session_id  and s.taddr=t.addr  and t.xidusn=r.usn

order by osuser, ss, obj;

--------------------------/*  to identify who is blocking whom when one process is blocking another from progressing due to lock issues.*/-----------------

select owner||'.'||object_name obj   ,oracle_username||' ('||s.status||')' oruser   ,os_user_name osuser   ,machine computer   ,

l.process unix   ,s.sid||','||s.serial# ss   ,r.name rs   ,to_char(s.logon_time,'yyyy/mm/dd hh24:mi:ss') time

from v$locked_object l ,dba_objects o ,v$session s ,v$transaction t ,v$rollname r

where l.object_id = o.object_id  and s.sid=l.session_id  and s.taddr=t.addr  and t.xidusn=r.usn

order by osuser, ss, obj;

--------------------

select decode(count(*),0,'There are no blocking locks.',1,'There is 1 blocking lock:',           'There are '||count(*)||' blocking locks:')

from All_Objects o, v$Session sw, v$lock lw, v$Session sh, v$lock lh

where lh.id1  = o.object_id  and  lh.id1  = lw.id1  and  sh.sid  = lh.sid  and  sw.sid  = lw.sid  and  sh.lockwait is null

and  sw.lockwait is not null  and  lh.type = 'TM'  and  lw.type = 'TM';

------------------------------------

SELECT username, gv$lock.sid,

TRUNC(id1/power(2,16)) rbs,

BITAND(id1,TO_NUMBER('ffff','xxxx'))+0 slot, id2 seq, lmode, request

FROM gv$lock, gv$session

WHERE gv$lock.type = 'TX'

AND gv$lock.sid = gv$session.sid;

----AND gv$session.username = 'CLEANSER';

SELECT XIDUSN, XIDSLOT, XIDSQN

FROM gv$transaction;

--------------------------------/*********      ACTIVE TABLE LOCKS

SELECT SUBSTR(a.object,1,25) TABLENAME,SUBSTR(s.username,1,15) USERNAME,SUBSTR(p.pid,1,5) PID,SUBSTR(p.spid,1,10) SYSTEM_ID,

DECODE(l.type,'RT','Redo Log Buffer','TD','Dictionary','TM','DML','TS','Temp Segments','TX','Transaction','UL','User','RW','Row Wait',l.type) LOCK_TYPE

FROM gv$access a, gv$process p, gv$session s, gv$lock l

WHERE s.sid = a.sid AND s.paddr = p.addr AND l.sid = p.pid

GROUP BY a.object, s.username, p.pid, l.type, p.spid

ORDER BY a.object, s.username;

----------------------------------------/****************** ACTIVE LOCKS

SELECT s.username, s.sid, s.serial#, s.osuser, k.ctime, o.object_name object, k.kaddr,

        DECODE(l.locked_mode,1, 'No Lock',2, 'Row Share',3, 'Row Exclusive',4, 'Shared Table',5, 'Shared Row Exclusive',6, 'Exclusive') locked_mode,

        DECODE(k.type,

    'BL','Buffer Cache Management (PCM lock)',

  'CF','Controlfile Transaction',

  'CI','Cross Instance Call',

  'CU','Bind Enqueue',

  'DF','Data File',

  'DL','Direct Loader',

  'DM','Database Mount',

  'DR','Distributed Recovery',

  'DX','Distributed Transaction',

  'FS','File Set',

  'IN','Instance Number',

  'IR','Instance Recovery',

  'IS','Instance State',

  'IV','Library Cache Invalidation',

  'JQ','Job Queue',

  'KK','Redo Log Kick',

  'LA','Library Cache Lock',

  'LB','Library Cache Lock',

  'LC','Library Cache Lock',

  'LD','Library Cache Lock',

  'LE','Library Cache Lock',

  'LF','Library Cache Lock',

  'LG','Library Cache Lock',

  'LH','Library Cache Lock',

  'LI','Library Cache Lock',

  'LJ','Library Cache Lock',

  'LK','Library Cache Lock',

  'LL','Library Cache Lock',

  'LM','Library Cache Lock',

  'LN','Library Cache Lock',

  'LO','Library Cache Lock',

  'LP','Library Cache Lock',

  'MM','Mount Definition',

  'MR','Media Recovery',

  'NA','Library Cache Pin',

  'NB','Library Cache Pin',

  'NC','Library Cache Pin',

  'ND','Library Cache Pin',

  'NE','Library Cache Pin',

  'NF','Library Cache Pin',

  'NG','Library Cache Pin',

  'NH','Library Cache Pin',

  'NI','Library Cache Pin',

  'NJ','Library Cache Pin',

  'NK','Library Cache Pin',

  'NL','Library Cache Pin',

  'NM','Library Cache Pin',

  'NN','Library Cache Pin',

  'NO','Library Cache Pin',

  'NP','Library Cache Pin',

  'NQ','Library Cache Pin',

  'NR','Library Cache Pin',

  'NS','Library Cache Pin',

  'NT','Library Cache Pin',

  'NU','Library Cache Pin',

  'NV','Library Cache Pin',

  'NW','Library Cache Pin',

  'NX','Library Cache Pin',

  'NY','Library Cache Pin',

  'NZ','Library Cache Pin',

  'PF','Password File',

  'PI','Parallel Slaves',

  'PR','Process Startup',

  'PS','Parallel Slave Synchronization',

  'QA','Row Cache Lock',

  'QB','Row Cache Lock',

  'QC','Row Cache Lock',

  'QD','Row Cache Lock',

  'QE','Row Cache Lock',

  'QF','Row Cache Lock',

  'QG','Row Cache Lock',

  'QH','Row Cache Lock',

  'QI','Row Cache Lock',

  'QJ','Row Cache Lock',

  'QK','Row Cache Lock',

  'QL','Row Cache Lock',

  'QM','Row Cache Lock',

  'QN','Row Cache Lock',

  'QO','Row Cache Lock',

  'QP','Row Cache Lock',

  'QQ','Row Cache Lock',

  'QR','Row Cache Lock',

  'QS','Row Cache Lock',

  'QT','Row Cache Lock',

  'QU','Row Cache Lock',

  'QV','Row Cache Lock',

  'QW','Row Cache Lock',

  'QX','Row Cache Lock',

  'QY','Row Cache Lock',

  'QZ','Row Cache Lock',

  'RT','Redo Thread',

  'SC','System Commit number',

  'SM','SMON synchronization',

  'SN','Sequence Number',

  'SQ','Sequence Enqueue',

  'SR','Synchronous Replication',

  'SS','Sort Segment',

  'ST','Space Management Transaction',

  'SV','Sequence Number Value',

  'TA','Transaction Recovery',

  'TM','DML Enqueue',

  'TS','Table Space (or Temporary Segment)',

  'TT','Temporary Table',

  'TX','Transaction',

  'UL','User-defined Locks',

  'UN','User Name',

  'US','Undo segment Serialization',

  'WL','Writing redo Log',

  'XA','Instance Attribute Lock',

  'XI','Instance Registration Lock') type

FROM gv$session s, sys.gv$lock c, sys.gv$locked_object l,

     dba_objects o, sys.gv$lock k, gv$lock v

WHERE o.object_id = l.object_id

AND l.session_id = s.sid

AND k.sid = s.sid

AND s.saddr = c.addr

AND k.kaddr = c.kaddr

AND k.kaddr = v.kaddr

AND v.addr = s.saddr

AND k.lmode = l.locked_mode

AND k.lmode = c.lmode

AND k.request = c.request

ORDER BY object;

--------------------/******** LIST LOCKS

SELECT session_id,lock_type, mode_held, mode_requested, blocking_others, lock_id1

FROM dba_lock l

WHERE lock_type NOT IN ('Media Recovery', 'Redo Thread');

--------------------------------/************* LOCKED OBJECTS

SELECT oracle_username USERNAME, owner OBJECT_OWNER,object_name, object_type, s.osuser,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 gv$locked_object v, dba_objects d,gv$lock l, gv$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;

------------(OR)

SELECT o.owner, o.object_name, o.object_type, o.last_ddl_time, o.status, l.session_id, l.oracle_username, l.locked_mode

FROM dba_objects o, gv$locked_object l

WHERE o.object_id = l.object_id;

----------(OR)

SELECT DECODE(request,0,'Holder: ','Waiter:') || sid sess, id1, id2, lmode, request, type

FROM gv$lock

WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM gv$lock WHERE request>0)

ORDER BY id1, request;

--------------------------------/*************   OBJECTS THAT HAVE BEEN LOCK FOR 2 MINUTES OR MORE

SELECT SUBSTR(TO_CHAR(w.session_id),1,5) WSID, p1.spid WPID,SUBSTR(s1.username,1,12) "WAITING User",SUBSTR(s1.osuser,1,8) "OS User",

        SUBSTR(s1.program,1,20) "WAITING Program",s1.client_info "WAITING Client",SUBSTR(TO_CHAR(h.session_id),1,5) HSID, p2.spid HPID,

        SUBSTR(s2.username,1,12) "HOLDING User",SUBSTR(s2.osuser,1,8) "OS User",SUBSTR(s2.program,1,20) "HOLDING Program",s2.client_info "HOLDING Client",

        o.object_name "HOLDING Object"

FROM gv$process p1, gv$process p2, gv$session s1,gv$session s2, dba_locks w, dba_locks h, dba_objects o

WHERE w.last_convert > 120 AND h.mode_held != 'None' AND h.mode_held != 'Null' AND w.mode_requested != 'None' AND s1.row_wait_obj# = o.object_id

    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 = s1.sid (+)

    AND h.session_id = s2.sid (+) AND s1.paddr = p1.addr (+) AND s2.paddr = p2.addr (+)

ORDER BY w.last_convert desc;