How to catch Oracle DB Lock Culprit

Hello,

If you have your data in Oracle database, you might want to know who (program, users) has caused a database lock. The follwing script will help you find out more about responsible program:

select
(select substr(username,1,10) from gv$session where sid=a.sid) as locker_name,
(select substr(osuser,1,10) from gv$session where sid=a.sid) as locker_osname,
(select substr(machine,1,20) from gv$session where sid=a.sid) as locker_machine,
(select substr(program,1,10) from gv$session where sid=a.sid) as locker_program,
a.sid, ' is blocking ',
(select substr(username,1,10) from gv$session where sid=b.sid) lockee_name,
(select substr(osuser,1,10) from gv$session where sid=b.sid) as lockee_osname,
(select substr(program,1,10) from gv$session where sid = b.sid) as lockee_program,
b.sid,
' with this dml:',
s.sql_text
from
gv$lock a, gv$lock b, v$open_cursor o, v$sql s
where
a.block = 1
and b.request > 0
and a.sid = o.sid
and o.sql_id = s.sql_id
and a.id1 = b.id1
and a.id2 = b.id2;


You should connect to your database as SYS as SYSADM in order to be able to run this script. Columns of this script are self-explained. Use the last column to find the spot in the locker program to fix the issue.

Your next question might be how to kill the Oracle session responsible for this lock? There are two ways:
1 - In command prompt type: OraKill OraSID PID (PID comes from v$session)
2 - In SqlPlus: alter system kill session 'sid,serial#' immediate;

Be a winner in your daily challenges.
Ali Khademi