LOCK – Scripts

Scripts to help identify Locks
1.)
 col sid format a10;
select decode(a.lmode,0,'|_','') || to_char(a.sid) sid, a.id1, a.type, a.lmode, a.ctime,
 decode(a.block,0,'blocked','*** Blocker Session *** ') Status
 from v$lock a
 where
 a.id1 in
 (select id1 from v$lock where block>0)
 order by a.ID1, lmode desc, block desc, a.ctime desc
 /
select inst_id, sid, serial#, username, module, program, round(last_call_et/60,0) Minutes, client_info
 from gv$session where
 (inst_id, sid) in
 (
 select blocking_instance, blocking_session
 from gv$session where blocking_session is not null
 );
SELECT vh.sid locking_sid,
 vs.status status,
 vs.program program_holding,
 vw.sid waiter_sid,
 vsw.program program_waiting
 FROM v$lock vh,
 v$lock vw,
 v$session vs,
 v$session vsw
 WHERE     (vh.id1, vh.id2) IN (SELECT id1, id2
 FROM v$lock
 WHERE request = 0
 INTERSECT
 SELECT id1, id2
 FROM v$lock
 WHERE lmode = 0)
 AND vh.id1 = vw.id1
 AND vh.id2 = vw.id2
 AND vh.request = 0
 AND vw.lmode = 0
 AND vh.sid = vs.sid
 AND vw.sid = vsw.sid;
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
 id1, id2, lmode, request, type
 FROM V$LOCK
 WHERE (id1, id2, type) IN
 (SELECT id1, id2, type FROM V$LOCK WHERE request>0)
 ORDER BY id1, request ;
SELECT o.subobject_name, L.SESSION_ID,L.ORACLE_USERNAME,L.OS_USER_NAME,L.LOCKED_MODE,
 O.OWNER,O.OBJECT_NAME,O.OBJECT_TYPE
 FROM V$LOCKED_OBJECT L,
 DBA_OBJECTS O
 WHERE L.OBJECT_ID = O.OBJECT_ID;
2.)
 select
 b.inst_id "Inst waiter",
 c.inst_id "Inst blocker",
 a.waiting_session "waitingSession", a.holding_session "Holding Session",
 d.SQL_TEXT "CMD waiting",
 e.SQL_TEXT "CMD Blocking",e.address,
 a.lock_type, a.mode_held, a.lock_id1, a.lock_id2, f.spid "Pid Blocker"
 from dba_waiters a, gv$session b, gv$session c, gv$sql d, gv$sql e, gv$process f
 where
 c.paddr=f.addr and
 a.waiting_session=b.sid and
 a.holding_session=c.sid and
 b.SQL_ADDRESS=d.ADDRESS(+) and
 c.sql_address=e.ADDRESS(+);
3.)
 select distinct b.last_call_et, a.holding_session, b.username, b.osuser, b.module, b.machine, b.program, round(b.last_call_et/60,2) Minutos , substr(c.sql_text,1,60) sql_text
 from dba_waiters a, v$session b, v$sql c
 where
 a.holding_session=b.sid and
 b.sql_address=c.address and
 a.waiting_session in
 (
 select sid from v$session_wait where event like 'enq%'
 )
 order by 1 desc
 /
select distinct 'alter system kill session ' || chr(39) || to_char(sid) || ',' || to_char(serial#) || chr(39) || ';'
 from dba_waiters a, v$session b
 where
 a.holding_session=b.sid and
 a.waiting_session in
 (
 select sid from v$session_wait where event like 'enq%'
 )
 order by 1 desc
 /
Advertisements