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

一、 Oracle数据库内操作--单实例 select alter system kill session ||s.sid||,||s.SERIAL#|| immediate; from v$session s where s.statusINACTIVE --状态为非活跃 and s.USERNAME ZZZ --用户为ZZZZ and s.typeBACKGROUND --不为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掉所有localno的非本地连接进程 ps -ef|grep -v grep|grep LOCALNO|awk {print $2}|xargs kill -9二、 SQL Serverkill 单个会话并查看回滚进度kill spid kill spid with statusonlykill 所有LCK相关被阻塞会话select kill cast(spid as varchar) FROM sys.sysprocesses sp where spid50 and blocked !0 and spid ! blocked and lastwaittype like LCK% and loginameXXX;kill 所有LCK相关阻塞源会话select kill cast(blocked as varchar) FROM sys.sysprocesses sp where spid50 and spid ! blocked and lastwaittype like LCK% and loginameXXX;根据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 Serverkill 指定DB所有会话DECLARE DBNAME NVARCHAR(100) DECLARE SQL NVARCHAR(MAX) DECLARE SPID NVARCHAR(100) SET DBNAMEdbname -- 要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 Nkill 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 datnameZZZ --涉及到的数据库名 and usenameZZZ --涉及到的用户名 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 datnamedevice_manager and usenameapp_rw and stateactive 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 12 ) 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 datnamedevice_manager and usenameapp_rw and stateactive 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 datnameXXX and usenameYYY and stateactive 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 datnameXXX and usenameYYY and stateactive and query like %ZZZ% ) a \watch 5;四、 MySQL1. awsselect concat(call mysql.rds_kill(,id,);) from information_schema.processlist where userZZZ 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.txt2. 阿里云select concat(KILL ,id,;) from information_schema.processlist where userZZZ -- 操作的数据库用户 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.txt3. 内网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