How can a client cancel an active Oracle query?

oracle, sql

Solution

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

Problem

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

Original source