在 PL/SQL 或 SQL 中使用循环杀死 oracle 会话
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10087686/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
kill oracle session using loop within PL/SQL or SQL
提问by Data-Base
we have this query:
我们有这个查询：
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
I need to kill all sessions which have the same SQL ID
我需要终止所有具有相同 SQL ID 的会话
I'm not sure how to connect things together, but I have this so far:
我不确定如何将事情联系在一起，但到目前为止我有这个：
for rec in (SELECT se.sid,ss.serial#
FROM v$session ss, v$sesstat se, v$statname sn
WHERE se.statistic# = sn.statistic#
AND name like '%CPU used by this session%'
AND se.sid = ss.sid
AND ss.status = 'ACTIVE'
AND ss.username is not null
AND ss.sql_id ='f7frew3erwe'
ORDER BY value ASC) loop
ALTER SYSTEM KILL SESSION 'rec.sid,rec.serial#' IMMEDIATE; //this is the tricky part!
end loop;
Any suggestions?
有什么建议？
回答by Ben
The question is similar to How can I kill all sessions connecting to my oracle database?, though for not for all sessions.
问题类似于如何终止连接到我的 oracle 数据库的所有会话？，但并非适用于所有会话。
You need to use execute immediatein order to alter the system in a PL/SQL block:
您需要使用execute immediate以更改 PL/SQL 块中的系统：
execute immediate 'Alter System Kill Session '''|| rec.Sid
|| ',' || rec.Serial# || ''' IMMEDIATE';
I query your need to use all the additional system tables. Something like the following query maysuffice if all you want to kill is a specific SQL_ID:
我询问您是否需要使用所有附加系统表。如果您想要杀死的只是一个特定的，则类似于以下查询的内容可能就足够了SQL_ID：
SELECT se.sid,ss.serial#
FROM v$session
WHERE status = 'ACTIVE'
AND username is not null
AND sql_id ='f7frew3erwe'
