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.
