oracle查看和杀死session

最新推荐文章于 2024-07-10 08:57:28 发布

weixin_33949359

最新推荐文章于 2024-07-10 08:57:28 发布

阅读量264

点赞数

文章标签：数据库

原文链接：https://my.oschina.net/ldm95/blog/719556

版权

为什么80%的码农都做不了架构师？>>>

http://www.cnblogs.com/sopost/archive/2010/09/30/2190078.html

查看所有session
SELECT substr(v$lock.sid,1,4) "SID",
substr(username,1,12) "UserName",
substr(object_name,1,25) "ObjectName",
v$lock.type "LockType",
decode(rtrim(substr(lmode,1,4)),
'2','Row-S (SS)','3','Row-X (SX)',
'4','Share', '5','S/Row-X (SSX)',
'6','Exclusive', 'Other' ) "LockMode",
substr(v$session.program,1,25) "ProgramName"
FROM V$LOCK,SYS.DBA_OBJECTS,V$SESSION
WHERE (OBJECT_ID = v$lock.id1
AND v$lock.sid = v$session.sid
AND username IS NOT NULL
AND username NOT IN ('SYS','SYSTEM')
AND SERIAL# != 1);

查看死锁session
select b.owner, b.object_name, a.session_id, a.locked_mode
from v$locked_object a, dba_objects b
where b.object_id = a.object_id

杀死session
select saddr,sid,serial#,paddr,username,status from v$session where username is not null;
alter system kill session 'sid,serial#' ;
例如：
SQL> select saddr,sid,serial#,paddr,username,status from v$session where username is not null;

SADDR SID SERIAL# PADDR USERNAME STATUS
-------- ---------- ---------- -------- ------------------------------ --------
542E0E6C 11 314 542B70E8 EYGLE INACTIVE
542E5044 18 662 542B6D38 SYS ACTIVE

SQL> alter system kill session '11,314';

System altered.

SQL> select saddr,sid,serial#,paddr,username,status from v$session where username is not null;

SADDR SID SERIAL# PADDR USERNAME STATUS
-------- ---------- ---------- -------- ------------------------------ --------
542E0E6C 11 314 542D6BD4 EYGLE KILLED
542E5044 18 662 542B6D38 SYS ACTIVE

转载于:https://my.oschina.net/ldm95/blog/719556