Monday, September 29, 2008

How to release dead lock in Oracle

1. Find out who is locking in Deadlock scenario

SQL> select (select username from v$session where sid=a.sid) blocker,
a.sid, ' is blocking ',
(select username from v$session where sid=b.sid) blockee,
b.sid
from v$lock a, v$lock b
where a.block = 1 and b.request > 0
and a.id1 = b.id1 and a.id2 = b.id2

/BLOCKER SID 'ISBLOCKING' BLOCKEE SID------------------------------ ----------ORAUSER 94 is blocking OPIS 1032.

2. Find out SID and Serial#

SQL> SELECT s.sid, s.serial#, s.osuser,FROM v$session swhere s.sid=94SQL> SID SERIAL# OSUSER PROGRAM---------- ---------- -------------------------------94 39897 mross3.

Kill the session:

SQL> alter system kill session '94,39897';

System altered.

No comments: