ORACLE KILL SESSION (oracle杀死死锁的线程)

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