oracle find lock #database #sql

oracle-lock.sql

select 'alter system kill session ' || chr(39) || s.sid || ', ' || s.serial# || chr(39) || ' ' || 'immediate'|| ';', s.inst_id,
'kill -9 ' || p.spid, s.username, s.module
from gv$session s, gv$process p
where p.inst_id = s.inst_id
and p.addr = s.paddr
and s.type <> 'BACKGROUND'
and s.sid in (
SELECT DECODE(request, 0, sid, -1) sid
FROM gV$LOCK
WHERE (id1, id2, TYPE) IN (SELECT id1, id2, TYPE FROM GV$LOCK WHERE lmode = 0)
);