合 Oracle、SQL Server、PG、GP、MySQL等数据库批量kill杀会话汇总

2023年4月28日

0 409 1

Tags： Oracle PG GreenPlum MSSQL MySQL 杀会话汇总

👉 本文共约820个字，系统预计阅读时间或需4分钟。

一、 Oracle
二、 SQL Server
三、 postgresql或GreenPlum
四、 MySQL
1. aws
2. 阿里云
3. 内网
3.1 数据库内操作
3.2 操作系统中操作（要求登录到数据库主机）
参考

一、 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') -- 会话登录时间

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

1 2	# 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

1 2	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';

1 2	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相关阻塞源会话

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！

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

1 2	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%';

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

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或GreenPlum

不要在操作系统层直接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执行时间长的

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;

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;

-- 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

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

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 time,concat('KILL ',id,';') from information_schema.processlist
where user='ZZZ'  --操作的数据库用户
and info like '%ZZZ%'   –当前消耗高的SQL语句
order by time desc; --根据操作时间排序，先kill执行时间长的
-- 查询结果输出到文件
select time,concat('KILL ',id,';') from information_schema.processlist
where user='ZZZ'  --操作的数据库用户
and info like '%ZZZ%' into outfile '/tmp/kill_session.sql';
-- 执行生成的sql文件
source /tmp/kill_session.sql;

select time,concat('KILL ',id,';') from information_schema.processlist

where user='ZZZ' --操作的数据库用户

and info like '%ZZZ%' –当前消耗高的SQL语句

order by time desc; --根据操作时间排序，先kill执行时间长的

-- 查询结果输出到文件

select time,concat('KILL ',id,';') from information_schema.processlist

where user='ZZZ' --操作的数据库用户

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

参考

https://blog.csdn.net/Hehuyi_In/article/details/100926923?spm=1001.2014.3001.5502

打赏赞(1)

标签： Oracle PG GreenPlum MSSQL MySQL 杀会话汇总

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

发表回复取消回复

要发表评论，您必须先登录。

合 Oracle、SQL Server、PG、GP、MySQL等数据库批量kill杀会话汇总

一、 Oracle

二、 SQL Server

三、 postgresql或GreenPlum

四、 MySQL

1. aws

2. 阿里云

3. 内网

3.1 数据库内操作

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

参考

相关文章

您可能还喜欢...

发表回复取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

2024 年 11 月
一	二	三	四	五	六	日
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30

合 Oracle、SQL Server、PG、GP、MySQL等数据库批量kill杀会话汇总

一、 Oracle

二、 SQL Server

三、 postgresql或GreenPlum

四、 MySQL

1. aws

2. 阿里云

3. 内网

3.1 数据库内操作

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

参考

相关文章

您可能还喜欢...

Oracle rac数据库备份集恢复到单机环境

Oracle 12c 手动创建CDB和非CDB

Greenplum中的AO表简介

发表回复 取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

发表回复取消回复