是否可以在不终止会话的情况下杀死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屋！