oracle、sqlserver、postgresql、mysql 批量kill会话脚本汇总

一、 Oracle

数据库内操作

--单实例： select 'alter system kill session '''||s.sid||','||s.SERIAL#||''' immediate;' from v$session s where s.status='INACTIVE' --状态为非活跃 and s.USERNAME= 'ZZZ' --用户为ZZZZ and s.type<>'BACKGROUND' --不为oracle后台进程 and program not like '%(J0%' --不为oracle的JOB进程 and s.LOGON_TIME >= to_date('2020-09-12 08:00:00','YYYY-MM-DD HH24:MI:SS') -- 会话登录时间

操作系统中操作（要求登录到数据库主机）

# kill掉所有local=no的非本地连接进程 ps -ef|grep -v grep|grep LOCAL=NO|awk '{print $2}'|xargs kill -9

二、 SQL Server

kill 单个会话并查看回滚进度

kill <spid> kill <spid> with statusonly

kill 所有LCK相关被阻塞会话

select 'kill '+cast(spid as varchar) FROM sys.sysprocesses sp where spid>50 and blocked !=0 and spid != blocked and lastwaittype like 'LCK%' and loginame='XXX';

kill 所有LCK相关阻塞源会话

select 'kill '+cast(blocked as varchar) FROM sys.sysprocesses sp where spid>50 and spid != blocked and lastwaittype like 'LCK%' and loginame='XXX';

根据sql文本kill会话（适用于大量慢查询）

SELECT distinct concat('kill ',session_id), SUBSTRING(qt.text, (er.statement_start_offset / 2) + 1, ((CASE er.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text) ELSE er.statement_end_offset END - er.statement_start_offset) / 2) + 1) AS stmt FROM sys.dm_exec_requests er CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) qt WHERE er.session_Id > 50 and SUBSTRING(qt.text, (er.statement_start_offset / 2) + 1, ((CASE er.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text) ELSE er.statement_end_offset END - er.statement_start_offset) / 2) + 1) like '%xxxx%';

kill阻塞中较低权重sql可参考

Detect and Automatically Kill Low Priority Blocking Sessions in SQL Server

kill 指定DB所有会话

DECLARE @DBNAME NVARCHAR(100) DECLARE @SQL NVARCHAR(MAX) DECLARE @SPID NVARCHAR(100) SET @DBNAME='dbname' -- 要kill掉连接的数据库名 DECLARE CurDBName CURSOR FOR SELECT [spid] FROM sys.sysprocesses WHERE [spid]>=50 AND DBID =DB_ID(@DBNAME) OPEN CurDBName FETCH NEXT FROM CurDBName INTO @SPID WHILE @@FETCH_STATUS = 0 BEGIN --kill process SET @SQL = N'kill '+@SPID EXEC (@SQL) FETCH NEXT FROM CurDBName INTO @SPID END CLOSE CurDBName DEALLOCATE CurDBName

三、 postgresql

不要在操作系统层直接kill 进程，即使是用户进程，被kill后也很可能导致pg直接挂掉，加重故障。

以下均为数据库内操作

• 方案一，较保守、风险低，但是针对高并发的系统效果不好。因为kill的速度慢，跟不上再次上来的会话。

SELECT 'select pg_terminate_backend('||pid||');' FROM pg_stat_activity WHERE pid <> pg_backend_pid() -- 不kill掉自己的进程 and datname='ZZZ' --涉及到的数据库名 and usename='ZZZ' --涉及到的用户名 and query like '%ZZZ%' – 涉及到的语句 order by (now()-query_start) desc; – 根据执行时间长短排序，先kill执行时间长的

• 方案二，针对高并发的情况，循环kill符合条件的会话至还剩1000个

with tmp3 as (select count(*) as cnt from pg_stat_activity WHERE pid <> pg_backend_pid() and datname='device_manager' and usename='app_rw' and state='active' and query like '%update%') select case when cnt <= 1000 then (with tmp1 as ( select pg_terminate_backend(pid) from (select pid from pg_stat_activity WHERE 1=2 ) as foo1) select count(*) from tmp1 ) when cnt > 1000 then (with tmp2 as ( select pg_terminate_backend(pid) from (select pid from pg_stat_activity WHERE pid <> pg_backend_pid() and datname='device_manager' and usename='app_rw' and state='active' and query like '%update%' order by backend_start limit 100) as foo2) select count(*) from tmp2 ) end as kill_if_too_many_process from tmp3 \watch 1;

• 方案三，循环kill完符合条件的会话，更暴力

-- 1. 先确认pid对应的sql是需要kill的sql，没有别的类似相似的sql干扰： SELECT pid,query FROM pg_stat_activity WHERE pid <> pg_backend_pid() and datname='XXX' and usename='YYY' and state='active' and query like '%ZZZZZZZZ%' order by (now()-query_start) desc; -- 2. 然后批量循环kill session select pg_terminate_backend(pid) from (SELECT pid FROM pg_stat_activity WHERE pid <> pg_backend_pid() and datname='XXX' and usename='YYY' and state='active' and query like '%ZZZ%' ) a \watch 5;

四、 MySQL

1. aws

select concat('call mysql.rds_kill(',id,');') from information_schema.processlist where user='ZZZ' and info like '%ZZZ%' -- 当前消耗高的SQL语句 and command = '' -- 按照SQL语句的状态 order by time desc; -- 在SQL命令行得到的kill命令不能直接粘贴复制，可通过shell命令快速得到kill id的脚本 mysql -uroot -p -h xxxx < kill_query.sh > kill_id.txt

2. 阿里云

select concat('KILL ',id,';') from information_schema.processlist where user='ZZZ' -- 操作的数据库用户 and info like '%ZZZ%' -- 当前消耗高的SQL语句 and command = '' -- 按照SQL语句的状态 order by time desc; -- 根据操作时间排序，先kill执行时间长的； -- 在SQL命令行得到的kill命令不能直接粘贴复制，可通过shell命令快速得到kill id的脚本 mysql -uroot -p -h xxxx < kill_query.sh > kill_id.txt

3. 内网

3.1 数据库内操作

select concat('KILL ',id,';') from information_schema.processlist where info like '%ZZZ%'; -- 查询结果输出到文件 select concat('KILL ',id,';') from information_schema.processlist where info like '%ZZZ%' into outfile '/tmp/kill_session.sql'; -- 执行生成的sql文件 source /tmp/kill_session.sql;

3.2 操作系统中操作（要求登录到数据库主机）

• 杀掉当前所有的MySQL连接

mysqladmin -uroot -p processlist|awk -F "|" '{print $2}'|xargs -n 1 mysqladmin -uroot -p kill

• 杀掉指定用户运行的连接，这里为Mike

# 假定kill掉所有ZZZ用户的线程 mysqladmin -uroot -p processlist|awk -F "|" '{if($3 == "Mike")print $2}'|xargs -n 1 mysqladmin -uroot -p kill