How To Quickly Terminate A Session Or Cancel A SQL Statement

Terminating sessions is something that DBAs are asked to do pretty often.
Usually the request comes in the form of an emergency, and people are waiting for you to terminate their session. The last thing you want to do in this case, is think about the command to use to accomplish the task.

As a DBA you have to be ready to act. What I mean by that, you have to have the step automated or semi-automated.

What does terminating a session mean? Terminating a session is the same as ending the session, or killing the session. That is why, people will often ask you to kill their session, instead of terminate their session.

When you terminate an active session (where the status is ACTIVE), the session ends. This process might take some time, as the transaction has to be rolled back.

An active session cannot be terminated when it performs network I/O or when it is rolling back a transaction. The operation the session is working on must complete, before it is terminated. The issuer of the kill statement, can wait up to 60 seconds for a session to be terminated. If the operation that cannot be terminated continues past the 60 seconds, then the issuer of the kill statement receives a message: ORA-00031: session marked for kill. The terminated session will have a status of KILLED in V$SESSION view.

The session that was terminated will receive the message: ORA-00028: your session has been killed, and will not be able to perform any operation, until it logs back in the database.

Since 18c there is another feature: you can cancel SQL Statements. What does that mean? It means you do not need to kill the session, you only terminate the SQL statement the session is running. The session who’s SQL got cancelled, will receive the message: ORA-01013: user requested cancel of current operation.

When cancelling the SQL statement, you have the option of specifying or not specifying the SQL ID of the statement the session is running.
If you specify the SQL ID, then that specific cursor will be cancelled, and resources freed up. Please note that a session could have multiple cursors open and active. If you do not specify the SQL ID, then the current statement that is running in that session is cancelled.

Here are the options for cancelling SQL:

ALTER SYSTEM CANCEL SQL 'SID, SERIAL';
ALTER SYSTEM CANCEL SQL 'SID, SERIAL, @INSTANCE_ID';
ALTER SYSTEM CANCEL SQL 'SID, SERIAL, SQL_ID';
ALTER SYSTEM CANCEL SQL 'SID, SERIAL, @INSTANCE_ID, SQL_ID';

Wether you are terminating a session, or cancelling the SQL statement, you will need to go through the same steps:

1) identify the session: SID, SERIAL#, instance number if applicable
2) kill the session or cancel the SQL

Identifying the session can be done by either the username, or if you know the SID.

select sid, serial# from v$session where username ='DROBETE';

Below is a script that gives you the statement to either kill a session or cancel the statement, pid.sql. You run the script by passing the SID as a parameter.

COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45
COLUMN Kill_Session_Command for A60


set linesize 200
set verify off
set define '&'
define _sid=&1


SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program,
s.sql_id
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND'
AND s.sid=&_sid;
select 'alter system kill session ' || '''' || s.sid|| ',' || s.serial# || ',@' || s.inst_id
|| ''';' as "Kill/Terminate_Session_Command"
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND'
AND s.sid=&&_sid
union
select 'alter system cancel sql ' || '''' || s.sid|| ',' || s.serial# || ',@' || s.inst_id || ''';'
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND'
AND s.sid=&&_sid


@pid.sql 593


INST_ID SID SERIAL# SPID USERNAME PROGRAM SQL_ID
-------- ------- ------- ------- ---------- ----------------- -------
1 593 37035 8901 HRAPP JDBC Thin Client


Kill/Terminate_Session_Command
----------------------------------------------
alter system cancel sql '593,37035,@1';
alter system kill session '593,37035,@1';

Feel free to use this script in your DBA tools!

If you enjoyed this article, and would like to learn more about databases, please sign up to my weekly email, and you will receive The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!