Oracle 杀掉 session 相关 sql 语句

大家好，这里是公众号 DBA学习之路，分享一些学习数据库路上的知识和经验。

kill某个等待事件对应的spid：

SQL

1set linesize 260 pagesize 10000
2select 'kill -9 ' || a.spid
3  from v$process a, v$session b
4 where a.addr = b.paddr
5   and a.background is null
6   and b.type = 'USER'
7   and b.event like '%' || '&eventname' || '%'
8   and b.status = 'ACTIVE';

对应的alter system kill session的语法：

SQL

1set linesize 260 pagesize 1000
2col machine for a50
3col kill_session for a60;
4select machine,
5       'alter system kill session ' || ''''||sid|| ',' || serial# ||''''|| 'immediate;' kill_session,
6       status
7  from v$session
8 where type='USER' and event like '%event_name%' and status = 'ACTIVE';

kill某个sql_id对应的spid：

SQL

1set linesize 260 pagesize 10000
2select 'kill -9 ' || a.spid
3  from v$process a, v$session b
4 where a.addr = b.paddr
5   and a.background is null
6   and b.type = 'USER'
7   and b.sql_id = '&sql_id'
8   and b.status = 'ACTIVE';

对应的alter system kill session的语法：

SQL

1set linesize 260 pagesize 10000
2col machine for a60
3select machine,
4       'alter system kill session ' || ''''||sid|| ',' || serial# ||''''|| 'immediate;',
5       status
6  from v$session
7 where sql_id = '&sql_id' and type='USER' and status='ACTIVE';

被kill会话的类型：

SQL

1set linesize 260 pagesize 10000
2select b.osuser,b.machine,b.program,b.sql_id,b.PREV_SQL_ID,a.spid,to_char(LAST_CALL_ET) as seconds,b.BLOCKING_SESSION,b.BLOCKING_INSTANCE
3  from v$process a, v$session b
4 where a.addr = b.paddr
5   and a.inst_id=b.inst_id
6   and a.background is null
7   and b.type = 'USER'
8   and b.event='&event_name'
9   and b.status = 'ACTIVE';

blocking会话类型和kill blocking会话：

SQL

1set linesize 260 pagesize 10000
2col machine for a50
3col kill_session for a60
4SELECT
5    blocking_instance,
6    blocking_session,
7    BLOCKING_SESSION_STATUS,
8    FINAL_BLOCKING_INSTANCE,
9    FINAL_BLOCKING_SESSION,
10    COUNT(*)
11FROM
12    v$session
13WHERE
14    upper(event) LIKE '%&cursor%'
15GROUP BY
16    blocking_instance,
17    blocking_session,
18    BLOCKING_SESSION_STATUS,
19    FINAL_BLOCKING_INSTANCE,
20    FINAL_BLOCKING_SESSION
21    order by blocking_instance,count(*);

kill blocking会话：

SQL

1select 
2       inst_id,
3       machine,
4       'alter system kill session ' || ''''||sid|| ',' || serial# ||''''|| 'immediate;' kill_session,
5       status
6  from gv$session a
7where a.type='USER' and (a.inst_id,a.sid) in 
8(
9select 
10BLOCKING_INSTANCE,
11BLOCKING_SESSION 
12from v$session
13where upper(event) like '%&cursor%'
14)
15order by inst_id;

所有含有关键字“LOCAL=NO”的进程是Oracle数据库中远程连接进程的共同特点，因此通过以下命令可以kill掉所有的进程

Bash

1ps -ef|grep -v grep|grep LOCAL=NO|awk '{print $2}'|xargs kill -9

往期精彩文章

本章目标：掌握本章核心知识点 前置要求：完成前序章节学习 预计时长：60 分钟

Oracle 一键巡检自动生成 Word 报告
 Oracle 一键安装合集
 Oracle一键安装脚本的 21 个疑问与解答
 Oracle一键巡检脚本的 21 个疑问与解答
 全网首发：Oracle 23ai 一键安装脚本（非 RPM）
Oracle 19C 最新 RU 补丁 19.24 ，一键安装！
Oracle Linux 7.9 一键安装 Oracle 19C
RedHat 9.4(aarch64) 一键安装 Oracle 19C
openEuler 22.03 LTS SP4 一键安装 Oracle 19C RAC
RHEL 7.9 一键安装 Oracle 19C 19.23 RAC
Oracle DataGuard GAP 修复手册
 优化 Oracle：最佳实践与开发规范
 DBA 必备：Linux 软件源配置全攻略
 Linux 一键配置时钟同步全攻略

1set linesize 260 pagesize 10000 2select 'kill -9 ' || a.spid 3 from v$process a, v$session b 4 where a.addr = b.paddr 5 and a.background is null 6 and b.type = 'USER' 7 and b.event like '%' || '&eventname' || '%' 8 and b.status = 'ACTIVE';

1set linesize 260 pagesize 1000 2col machine for a50 3col kill_session for a60; 4select machine, 5 'alter system kill session ' || ''''||sid|| ',' || serial# ||''''|| 'immediate;' kill_session, 6 status 7 from v$session 8 where type='USER' and event like '%event_name%' and status = 'ACTIVE';

1set linesize 260 pagesize 10000 2select 'kill -9 ' || a.spid 3 from v$process a, v$session b 4 where a.addr = b.paddr 5 and a.background is null 6 and b.type = 'USER' 7 and b.sql_id = '&sql_id' 8 and b.status = 'ACTIVE';

1set linesize 260 pagesize 10000 2col machine for a60 3select machine, 4 'alter system kill session ' || ''''||sid|| ',' || serial# ||''''|| 'immediate;', 5 status 6 from v$session 7 where sql_id = '&sql_id' and type='USER' and status='ACTIVE';

1set linesize 260 pagesize 10000 2select b.osuser,b.machine,b.program,b.sql_id,b.PREV_SQL_ID,a.spid,to_char(LAST_CALL_ET) as seconds,b.BLOCKING_SESSION,b.BLOCKING_INSTANCE 3 from v$process a, v$session b 4 where a.addr = b.paddr 5 and a.inst_id=b.inst_id 6 and a.background is null 7 and b.type = 'USER' 8 and b.event='&event_name' 9 and b.status = 'ACTIVE';

1set linesize 260 pagesize 10000 2col machine for a50 3col kill_session for a60 4SELECT 5 blocking_instance, 6 blocking_session, 7 BLOCKING_SESSION_STATUS, 8 FINAL_BLOCKING_INSTANCE, 9 FINAL_BLOCKING_SESSION, 10 COUNT(*) 11FROM 12 v$session 13WHERE 14 upper(event) LIKE '%&cursor%' 15GROUP BY 16 blocking_instance, 17 blocking_session, 18 BLOCKING_SESSION_STATUS, 19 FINAL_BLOCKING_INSTANCE, 20 FINAL_BLOCKING_SESSION 21 order by blocking_instance,count(*);

1select 2 inst_id, 3 machine, 4 'alter system kill session ' || ''''||sid|| ',' || serial# ||''''|| 'immediate;' kill_session, 5 status 6 from gv$session a 7where a.type='USER' and (a.inst_id,a.sid) in 8( 9select 10BLOCKING_INSTANCE, 11BLOCKING_SESSION 12from v$session 13where upper(event) like '%&cursor%' 14) 15order by inst_id;