是否可以在不终止会话的情况下终止 oracle 中的单个查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/466963/
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
Is it possible to kill a single query in oracle without killing the session?
提问by Ryan Ahearn
I would like to be able to kill a user's query in Oracle 10.2.0.4 without killing their entire session. This would allow the query to end, but not log that user out of their session, so they can continue making other queries. Is this possible at all? Or is the blunt hammer of killing the session the only way to go about ending a query's execution?
我希望能够在不终止整个会话的情况下终止 Oracle 10.2.0.4 中的用户查询。这将允许查询结束，但不会将该用户从其会话中注销，以便他们可以继续进行其他查询。这可能吗？或者杀死会话的钝锤是结束查询执行的唯一方法？
回答by Dave Costa
I found a trick. I have no idea how safe this is to play with, but it does work. There is an Oracle event, 10237, which is described as "simulate ^C (for testing purposes)".
我发现了一个技巧。我不知道这玩起来有多安全，但它确实有效。有一个 Oracle 事件 10237，它被描述为“模拟 ^C（用于测试目的）”。
You have to have the SID and SERIAL# of the session you want to interrupt.
您必须拥有要中断的会话的 SID 和 SERIAL#。
Call SYS.DBMS_SYSTEM.SET_EV( sid, serial#, 10237, 1, '' ) to activate the event in the target session. Any currently executing statement should be interrupted (receiving "ORA-01013: user requested cancel of current operation"). As long as the event is set, any further statements the session attempts to execute will immediately terminate with the same error.
调用SYS.DBMS_SYSTEM.SET_EV ( sid, serial#, 10237, 1, '' ) 以激活目标会话中的事件。任何当前正在执行的语句都应该被中断（收到“ORA-01013：用户请求取消当前操作”）。只要设置了事件，会话尝试执行的任何进一步语句都将立即终止，并出现相同的错误。
To deactivate the event, make the same call with the fourth parameter set to "0". The session will then be able to execute statements again.
要停用该事件，请在第四个参数设置为“0”的情况下进行相同的调用。然后会话将能够再次执行语句。
Note that the target session has to detect that the event is set, which may take time, or may never happen, depending on what it is doing. So you can't just quickly toggle the event on and off. You would need to turn it on, verify that the statement in question has stopped, then turn it off.
请注意，目标会话必须检测到事件已设置，这可能需要一些时间，也可能永远不会发生，具体取决于它在做什么。所以你不能只是快速打开和关闭事件。您需要将其打开，确认相关语句已停止，然后将其关闭。
Here's some sample code. This is meant to be run as an anonymous block in SQLPlus, with substitution variables "sid" and "serial" defined appropriately. You could turn it into a stored procedure with those as its parameters.
这是一些示例代码。这意味着在 SQLPlus 中作为匿名块运行，并适当定义了替换变量“sid”和“serial”。您可以将其转换为存储过程，并将这些作为其参数。
DECLARE
l_status v$session.status%TYPE;
BEGIN
dbms_system.set_ev( &sid, &serial, 10237, 1, '');
LOOP
SELECT status INTO l_status FROM v$session
WHERE sid = &sid and serial# = &serial;
EXIT WHEN l_status='INACTIVE';
END LOOP;
dbms_system.set_ev( &sid, &serial, 10237, 0, '');
END;
回答by cagcowboy
I suspect it might be possible since you can do this in TOAD. Whilst a query is running a Cancel dialog comes up which you can hit to stop the query.
我怀疑这可能是可能的，因为您可以在 TOAD 中执行此操作。当查询正在运行时，会出现一个取消对话框，您可以点击该对话框来停止查询。
How it's implemented I don't know, but would be very interested to find out too.
我不知道它是如何实现的，但也很想知道。
If you're using java there's the java.sql.Statement cancel() method which is supposed to do this. See here for some notes and limitations...
如果您使用的是 java，则应该使用 java.sql.Statement cancel() 方法来执行此操作。请参阅此处了解一些注意事项和限制...
http://download.oracle.com/docs/cd/B14117_01/java.101/b10979/tips.htm#BACDAICJ
http://download.oracle.com/docs/cd/B14117_01/java.101/b10979/tips.htm#BACDAICJ
回答by Gary Myers
You could look at Resource Limits:
您可以查看资源限制：
"If a user exceeds a call-level resource limit, then Oracle halts the processing of the statement, rolls back the statement, and returns an error. However, all previous statements of the current transaction remain intact, and the user's session remains connected."
“如果用户超过了调用级别的资源限制，那么 Oracle 将停止处理该语句，回滚该语句，并返回错误。但是，当前事务的所有先前语句保持不变，并且用户的会话保持连接。 ”
That assumes the reason for cancelling the SQL is a resource limit, rather than it updating the wrong set of rows (for example). I suspect you wouldn't be able to affect currently running SQL through adding a resource limit.
假设取消 SQL 的原因是资源限制，而不是更新错误的行集（例如）。我怀疑您无法通过添加资源限制来影响当前正在运行的 SQL。
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/security.htm#i13767
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dbrm.htm#i1010776
回答by Tanel Poder
Ideally the user's application should have the ability to cancel a query (via OCICancel or equivalent).
理想情况下，用户的应用程序应该能够取消查询（通过 OCICancel 或等效方法）。
Otherwise, the best would be to use Resource Manager (if on Oracle EE), you can use DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS to set the target session to consumer group CANCEL_SQL.
否则，最好使用资源管理器（如果在 Oracle EE 上），您可以使用 DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS 将目标会话设置为消费者组 CANCEL_SQL。
Another hack for cancelling other sessions query, which doesn't work with sqlplus windows client sessions though, would be to send urgent signal to the Oracle process using kill -URG (it's a hack, not for everyday use)
另一个取消其他会话查询的技巧，虽然它不适用于 sqlplus Windows 客户端会话，但它是使用 kill -URG 向 Oracle 进程发送紧急信号（这是一个技巧，不适合日常使用）
I've written about why it works here:
我已经写过它为什么在这里工作：
http://blog.tanelpoder.com/2010/02/17/how-to-cancel-a-query-running-in-another-session/
http://blog.tanelpoder.com/2010/02/17/how-to-cancel-a-query-running-in-another-session/
