KILL STATEMENT

KILL STATEMENT — kill a statement.

Syntax

KILL STATEMENT { execid | ( CONNID num HANDLE num COUNT num ) }

Description

Kill a statement by either specifying an execution ID, as reported in the EXECID column in SYSTEM.CONNECTIONS, or specifying a statement connection id, handle and count, as reported in the CONNID, HANDLE and COUNT columns respectively, in SYSTEM.CONNECTIONS.

The effects of the statement being killed are not made durable in the database. That is, the database is returned to a state where the statement was never executed. The connection in which the statement was killed is not affected and remains open.

Parameters

execid: The execution if of the executing SQL statement that you want to terminate, as reported in the EXECID column in SYSTEM.CONNECTIONS. Use SELECT execid FROM system.connections to find this parameter.
num: A number representing the statement connection id, handle or count as reported in the CONNID, HANDLE and COUNT columns respectively, in SYSTEM.CONNECTIONS. Use SELECT connid,handle,count FROM system.connections to find these parameters. If not specifying execid, these keywords are required and are case insensitive.

Example

Step 1 Find query IDs in the system.connections table

SELECT SQLSTRING, COUNT, CONNID, HANDLE, EXECID FROM system.connections;

                                SQLSTRING                                 COUNT    CONNID    HANDLE         EXECID
 ------------------------------------------------------------------------ ------ ----------- ------- --------------------
 SELECT SQLSTRING, COUNT, CONNID, HANDLE, EXECID FROM system.connections;   12   21474836583    1    18446744516091183109
 select msleep(100000) from dual;                                            1   21474836587    1    18446744533271052293

Step 2 Issue KILL STATEMENT using EXECID or CONNID,HANDLE, and COUNT

KILL STATEMENT 18446744533271052293;

/* or */

KILL STATEMENT CONNID 21474836587 HANDLE 1 COUNT 2;

An error is returned if the query is no longer running.