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了!
解决方案 »
- 对超出当月最大日期的字符型日期处理,如2012-02-30
- 求高手修改这个字段??
- wm_concat Ora-06502: Pl/sql: 数字或值错误 : 字符串缓冲区太小
- 新手-帮帮忙-oracle 日志
- 如何把同表中两个字段格式化成树型结构?
- 关于使用LSNRCTL STOP命令无法停掉监听器进程tnslsnr的问题
- 两个不同IP的数据库(UNIX/WIN 环境)可以用触发器把一个表的内容导到另一个库里的相应表吗?
- 帮忙看一下这条SQL语句的效率:获得一定范围内的日期
- 大侠帮忙!!!Oracle数据库恢复!!!
- 实在不好意思,请问如何在db2中将20020830字符串转换为日期型,或求'20020830'与'20020810'之间的日期天数差
- 不用检索如何直接得到一个查询的ResultSetMetaData?
- 如何控制返回行数范围?
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】