This was from my worst night this week, I’ve become to believe that people can do anything in a pinch situation. LOL
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
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 🙂