Tricks One:
1. View the locked table:
2. Select P.spid,c.object_name,b.session_id,b.oracle_username,b.os_user_name from V$process p,v$session A, V $locked _object b,all_objects C where p.addr=a.paddr and A.process=b.process and c.object_id=b.object_id
3.
4. Unlock:
5. Alter system kill session ' 146 ';(where 146 is the locked process number)
Trick Two:
1. SELECT Sn.username, M.sid,sn. serial#, M.type,
2. DECODE (M.lmode,
3.0, ' None ',
4.1, ' Null ',
5.2, ' Row Share ',
6.3, ' Row Excl. ',
7.4, ' Share ',
8.5, ' S/row Excl. ',
9.6, ' Exclusive ',
Lmode, LTRIM (To_char (Lmode, ' 990 '))
One.) Lmode,
DECODE (M.request,
0, ' None ',
1, ' Null ',
2, ' Row Share ',
3, ' Row Excl. ',
4, ' Share ',
5, ' S/row Excl. ',
6, ' Exclusive ',
Request, LTRIM (To_char (m.request, ' 990 '))
.) Request,
M.ID1, M.id2
. From V$session sn, V$lock m
WHERE (SN. SID = M.sid and M.request! = 0)--there is a lock request, which is blocked
. OR (SN. SID = M.sid--There are no lock requests, but locked objects are locked by other session requests
M.request = 0
Lmode! = 4
Id1 and (ID2) in (
SELECT S.id1, S.id2
From V$lock S
WHERE Request! = 0 and S.id1 = m.id1
S.id2 = M.id2)
33.)
ORDER by Id1, Id2, m.request;
35.
Alter system kill session ' 91 ';
The. Alter system kill session ' 144,633 ';
Alter system kill session ' 91,21 ';
. alter system kill session ' 112,5772 ';
March 15, 2010 20:44
Oracle Deadlock handling method