ORACLE KILL SESSION

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

转载最新推荐文章于 2024-07-10 08:57:28 发布 · 818 阅读

文章标签：

#session #kill #oracle #system #object #数据库

oracle PL/SQL编程专栏收录该内容

16 篇文章

订阅专栏

本文详细介绍了如何使用Oracle数据库进行KILLSESSION操作，包括查看锁定的表、解锁、根据SID查看会话执行的SQL语句及处理无法正常终止的会话。

ORACLE数据库KILL SESSION操作

---------------------------------------------------------------------------------------------
--1.查看锁定的表
---------------------------------------------------------------------------------------------

select /*+ rule */
lpad(' ', decode(l.xidusn, 0, 3, 0)) || l.oracle_username user_name,
o.owner,
o.object_name,
o.object_type,
s.sid,
s.serial#,
p.spid,
'alter system kill session ''' || s.sid || ',' || s.serial# || ''''
from v$locked_object l, dba_objects o, v$session s, v$process p
where l.object_id = o.object_id
and l.session_id = s.sid
and s.paddr = p.addr
order by o.object_id, xidusn desc;

---------------------------------------------------------------------------------------------
--2.解锁
---------------------------------------------------------------------------------------------

--alter system kill session '587,29964';

----------------------------------------------------------------------------------------------
--3.根据SID查看该session执行的SQL语句
----------------------------------------------------------------------------------------------

select *
    from v$sqltext v
   where v.HASH_VALUE =
         (select c.SQL_HASH_VALUE from v$session c where c.SID = 587)
   order by piece;

----------------------------------------------------------------------------------------------
--4.kill 不掉的session
----------------------------------------------------------------------------------------------

linux系统执行如下命令：

kill -9 spid;

windows系统执行：

orakill sid spid