Mark Harrison Mark Harrison - 7 months ago 35
SQL Question

How can a client cancel an active Oracle query?

I have an active Oracle operation which is taking too long. How can I programmatically cancel the operation without terminating the session?

Answer

The OCI library provides this functionality. The canonical example is a client pressing control-C during the long-running operation, getting control of the program back through the signal handler, and then cancelling the long-running operation.

Internally, the client will send an urgent message over the TCP connection, and the server side will process the termination via a SIGURG handler. The client operation will receive this status.

ORA-01013 ("user requested cancel of current operation")
  • C/C++:

OCIBreak()

http://docs.oracle.com/cd/B10500_01/appdev.920/a96584/oci16m96.htm

http://docs.oracle.com/cd/A97630_01/appdev.920/a96584/oci02bas.htm (at "Cancelling Calls")

  • Python (cx_Oracle):

conn.cancel()

http://cx-oracle.sourceforge.net/html/connection.html