Oracle check lock session

This was from my worst night this week, I’ve become to believe that people can do anything in a pinch situation. LOL

I was only grabbing from here and here

Do with your own risk.

check what’s was blocking :

select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;

You can check what object that locked using this command :

select object_name from dba_objects where object_id=88519 ;

where 88519 is the ID

But first, you must have the ID from SID provided by the first command.
To query look at the ID you can cast :

select id1 from v$lock where sid = 479;

where 479 is the SID

After you observing what caused your lock problem you can try unlock it.
First, try to query the serial# using this command below :

SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45

SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND' and SID = 479;

and…finally to unlock cast this :

ALTER SYSTEM KILL SESSION 'sid,serial#';

Please do make sure the sid you want to fuck with is the right one.

Happy killing 🙂