方法一:
1、查找死锁进程
SELECT s.username, l.OBJECT_ID,
''''||s.SID||','||s.SERIAL#||'''',
l.SESSION_ID, s.SERIAL#,
l.ORACLE_USERNAME, l.OS_USER_NAME, l.PROCESS
FROM V$LOCKED_OBJECT l, V$SESSION S
WHERE l.SESSION_ID = S.SID;
2、kill掉这个死锁的进程
alter system kill session 'sid,serial#';
3、仍然不能解决的:
select pro.spid
from v$session ses, v$process pro
where ses.sid = &SID
and ses.paddr = pro.addr;
4、关于某个表被锁
SELECT A.OWNER, ''''||C.SID||','||C.SERIAL#||'''',
A.OBJECT_NAME, B.SESSION_ID,
B.ORACLE_USERNAME, B.OS_USER_NAME, B.PROCESS, B.LOCKED_MODE,
C.SID, C.SERIAL#, C.PROGRAM
FROM ALL_OBJECTS A, V$LOCKED_OBJECT B, SYS.GV_$SESSION C
WHERE (A.OBJECT_ID = B.OBJECT_ID)
AND (B.PROCESS = C.PROCESS)
AND A.OBJECT_NAME = upper('TABLE_NAME');
方法二、
1、查哪个过程被锁
查V$DB_OBJECT_CACHE视图:
SELECT * FROM V$DB_OBJECT_CACHE WHERE OWNER='过程的所属用户' AND LOCKS!='0';
2、查是哪一个SID,通过SID可知道是哪个SESSION.
查V$ACCESS视图:
SELECT * FROM V$ACCESS WHERE OWNER='过程的所属用户' AND NAME='刚才查到的过程名';
3、查出SID和SERIAL#
查V$SESSION视图: SELECT SID,SERIAL#,PADDR FROM V$SESSION WHERE SID='刚才查到的SID'
查V$PROCESS视图: SELECT SPID FROM V$PROCESS WHERE ADDR='刚才查到的PADDR';
4、杀进程
(1)、先杀ORACLE进程:
ALTER SYSTEM KILL SESSION '查出的SID,查出的SERIAL#';
(2)、再杀操作系统进程:
KILL -9 刚才查出的SPID
或
ORAKILL 刚才查出的SID 刚才查出的SPID