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.
Pingback: OEM: The number of hanging transactions are hang_trans is % – GREPORA