Solving Simple Locks Through @lock2s and @killlocker

Hi guys!
This post is to show the most simple and most common kind of locks for objects and the simpliest way to solve it (killing the locker). 🙂
It’s so common that I scripted it. Take a look:

greporadb> @lock2s
 Inst        SID SERIAL# UserName  STATUS   LOGON_TIME          LMODE  REQUEST LC_ET TY       ID1        ID2       CTIME LOCKWAIT         EVENT                                                                                           
----- ---------- ------- --------- -------- ------------------- ------ ------- ----- -- ---------- ---------- ---------- ---------------- -----------------------------------                                                             
    1        354   18145 MATHEUS   ACTIVE   17/06/2016 14:25:19 X      NONE    4032  TX     393238     424490        715 00000000DB0DF900 enq: TX - row lock contention                                                                   
    1        169   25571 GREPORA   ACTIVE   17/06/2016 14:22:48 NONE   X        714  TX     393238     424490        714 00000000DB0D5ED8 enq: TX - row lock contention                                                                   
    1        252   63517 MATHEUS   INACTIVE 17/06/2016 14:17:49 X      NONE     714  TX     655363    1550347       4195                  SQL*Net message from client                                                                     
    1        846   65011 GREPORA   ACTIVE   17/06/2016 14:20:18 NONE   X       4075  TX     655363    1550347        715 00000000DB0ECB88 enq: TX - row lock contention                                                                   
    1        354   18145 GREPORA   ACTIVE   17/06/2016 14:25:19 NONE   S       4032  TX     655363    1550347        715 00000000DB0DF900 enq: TX - row lock contention                                                                   
5 rows selected.

You can identify the Locker by LMODE column. And all his Waiters by REQUEST column marked by not ‘NONE’, below each Locker…

So, let’s kill the lockers:

greporadb> @killlocker
'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||'''IMMEDIATE;'                                                                                                                                                                                                                                                                                                                                          
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                                       
alter system kill session '252,63517' immediate;                                                                                                                                                                                                                                                                                                                                                       
alter system kill session '354,18145' immediate;                                                                                                                                                                                                                                                                                                                                                       
2 rows selected.
greporadb> alter system kill session '252,63517' immediate;      
System altered.
greporadb> alter system kill session '354,18145' immediate;      
System altered.
greporadb> @lock2s
no rows selected

Solved!
My magic scripts? Here it goes:

get lock2s.sql:

set lines 10000
set trimspool on
col serial# for 999999
col lc_et for 999999
col l1name for a50
col lmode for a6
col username for a25
select /*+ rule */
distinct b.inst_id,a.sid,b.serial#,b.username,b.status,
--b.audsid,
--b.module,
--b.machine,b.osuser,
b.logon_time,
decode(lmode,1,'null',2,'RS',3,'RX',4,'S',5,'SRX',6,'X',0,'NONE',lmode) lmode,
decode(request,1,'null',2,'RS',3,'RX',4,'S',5,'SRX',6,'X',0,'NONE',request) request,
b.last_call_et LC_ET,a.type TY,a.id1,a.id2,
d.name||'.'||c.name l1name,a.ctime,b.lockwait,b.event
--distinct b.inst_id,a.sid,b.username,a.type,d.name||'.'||c.name l1name,a.id1,a.id2,
--decode(lmode,1,'null',2,'RS',3,'RX',4,'S',5,'SRX',6,'X',0,'NONE',lmode) lmode,
--decode(request,1,'null',2,'RS',3,'RX',4,'S',5,'SRX',6,'X',0,'NONE',request) request,a.ctime,b.lockwait,b.last_call_et
from gv$lock a, gv$session b,sys.obj$ c,sys.user$ d,(select a.id1 from gv$lock a where a.request  0) lock1
where a.id1    = c.OBJ# (+)
and a.sid      = b.sid
and c.owner#   = d.user# (+)
and a.inst_id=b.inst_id
and b.username is not null
and a.id1 = lock1.id1
order by id1,id2, lmode desc
/

get killlocker.sql:

select 'alter system kill session '''||sid||','||serial#||''' immediate;' 
from v$session where sid in (select BLOCKING_SESSION from v$session where BLOCKING_SESSION is not null);

Now you can put in your Linkedin you are a JR DBA… 😛
haha

Matheus.