1、利用如下语句查锁,记录下sid,和serial#
SELECT A.OWNER,
A.OBJECT_NAME,
B.XIDUSN,
B.XIDSLOT,
B.XIDSQN,
B.SESSION_ID,
B.ORACLE_USERNAME,
B.OS_USER_NAME,
B.PROCESS,
B.LOCKED_MODE,
C.MACHINE,
C.STATUS,
C.SERVER,
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
ORDER BY 1,2
2、SELECT spid FROM v$process
WHERE addr in (SELECT paddr FROM v$session WHERE sid=your_sid AND seral#=your_serial#);
并记录下spid
3、到操作系统上
oracle>kill -9 spid
一切便ok了!
SELECT A.OWNER,
A.OBJECT_NAME,
B.XIDUSN,
B.XIDSLOT,
B.XIDSQN,
B.SESSION_ID,
B.ORACLE_USERNAME,
B.OS_USER_NAME,
B.PROCESS,
B.LOCKED_MODE,
C.MACHINE,
C.STATUS,
C.SERVER,
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
ORDER BY 1,2
2、SELECT spid FROM v$process
WHERE addr in (SELECT paddr FROM v$session WHERE sid=your_sid AND seral#=your_serial#);
并记录下spid
3、到操作系统上
oracle>kill -9 spid
一切便ok了!
alter system kill session 'sid,serial#';
select nvl(S.USERNAME,'Internal') username,
nvl(S.TERMINAL,'None') terminal,
L.SID||','||S.SERIAL# Kill,
U1.NAME||'.'||substr(T1.NAME,1,20) tab,
decode(L.LMODE, 1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive',null) lmode,
decode(L.REQUEST,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive',null) request
from V$LOCK L, V$SESSION S, SYS.USER$ U1,SYS.OBJ$ T1
where L.SID = S.SID
and T1.OBJ# = DECODE(L.ID2,0,L.ID1,LDID2)
and U1.USER# = T1.OWNER#
and S.TYPE != 'BACKGROUND'
order by 1,2,5;
然后根据选出的结果执行
ALTER SYSTEM KILL SESSION 'session的SID';
当使用菜单退出或直接点右上角的按钮退出,就不会自动提交,
这是SQL*Plus的一个BUG。
ALTER SYSTEM DISCONNECT SESSION 'sid, serial#' POST_TRANSACTION【IMMEDIATE】