Is it possible to kill a single query in oracle without killing the session?

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?

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

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

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.

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.

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;

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.

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

http://download.oracle.com/docs/cd/B14117_01/java.101/b10979/tips.htm#BACDAICJ

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

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.

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

Ideally the user's application should have the ability to cancel a query (via OCICancel or equivalent).

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.

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)

I've written about why it works here:

http://blog.tanelpoder.com/2010/02/17/how-to-cancel-a-query-running-in-another-session/