杀掉(kill)Oracle中会话或死锁

-- 根据机器名称、SQL_HASH_VALUE过滤特殊的会话，并形成批量kill脚本

SELECT 'alter system kill session ''' || c.sid || '' || ',' || c.serial# ||''';'
FROM v$session c  
WHERE c.MACHINE='hffwbz1' and c.SQL_HASH_VALUE='2639167965'
-
 

--可以以Oralce管理员权限用户登录Oracle数据，查询到被锁的对象，然后杀除指定的会话。用下面的语句查询被锁的对象，可以带上更多约束条件，如schemaname等更精确的匹配。

SELECT a.object_id, a.session_id, b.object_name, c.*   
   FROM v$locked_object a, dba_objects b, v$session c   
WHERE a.object_id = b.object_id   
    AND a.SESSION_ID = c.sid(+)  

--杀Seesion的SQL语句语法如下：

alter system kill session 'sid, serial#'  

--如上面查出来的一条记录的sid是53, serial#为663,就执行以下的语句

alter system kill session '53,663' 

--拼凑kill语句的方式如下，下面加了一个过滤条件和一个排序，杀除真正关心的表，并且着重注意超时时间过长的会话。

SELECT 'alter system kill session ''' || c.sid || '' || ',' || c.serial# ||''';',  
       a.object_id, a.session_id, b.object_name, c.*  
FROM v$locked_object a, dba_objects b, v$session c  
WHERE a.object_id = b.object_id  
   AND a.SESSION_ID = c.sid(+)  
   AND schemaname = 'Unmi'  
ORDER BY logon_time

 

 

 

 

 

 

 

 

 

 

 

 

 

select a.OBJECT_ID,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 and a.OBJECT_ID=73576 

alter system kill session '15,16229';
 

--1
select status from v$instance;
--2 
 select SESSION_ID ,o.*
  from v$locked_object l
  join dba_objects o on (l.OBJECT_ID=o.object_id)
  join v$session s on l.OBJECT_ID=s.
 --SESSION_ID 128
 --3
 select * from v$session s where sid in (1260 ,1309,1179,1144,844,780,521,396,150,15)
 
  select * from v$session where sid in
  (select a.session_id
from v$locked_object a,dba_objects b
where b.object_id = a.object_id and a.OBJECT_ID=73576)

--alter session set events 'immediate trace name flush_cache level 1';

 

create index IND_BIZ_DAY_STATISTICS
ON BIZ_DAY_STATISTICS(stats_date,stats_type,CLIENT_ID)
TABLESPACE SLSTORAGE;