是否可以在不终止会话的情况下终止 oracle 中的单个查询? [英] Is it possible to kill a single query in oracle without killing the session?

问题描述

我希望能够在 Oracle 10.2.0.4 中终止用户的查询而不终止他们的整个会话.这将允许查询结束，但不会将该用户从其会话中注销，以便他们可以继续进行其他查询.这可能吗?或者杀死会话的钝锤是结束查询执行的唯一方法?

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 事件 10237，它被描述为模拟 ^C(用于测试目的)".

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)".

您必须拥有要中断的会话的 SID 和 SERIAL#.

You have to have the SID and SERIAL# of the session you want to interrupt.

调用 SYS.DBMS_SYSTEM.SET_EV( sid, serial#, 10237, 1, '' ) 以激活目标会话中的事件.任何当前正在执行的语句都应该被中断(收到ORA-01013:用户请求取消当前操作").只要设置了事件，会话尝试执行的任何进一步语句都将立即终止，并出现相同的错误.

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.

要停用该事件，请在第四个参数设置为0"的情况下进行相同的调用.然后会话将能够再次执行语句.

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.

请注意，目标会话必须检测到事件已设置，这可能需要时间，也可能永远不会发生，具体取决于它在做什么.所以你不能只是快速打开和关闭事件.您需要将其打开，确认相关语句已停止，然后将其关闭.

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.

这是一些示例代码.这意味着在 SQLPlus 中作为匿名块运行，并适当定义了替换变量sid"和serial".您可以将其转换为以这些为参数的存储过程.

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.

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;

这篇关于是否可以在不终止会话的情况下终止 oracle 中的单个查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！