ALTER SYSTEM KILL SESSION
The basic syntax for killing a session is shown below.
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';
In a RAC environment, you optionally specify the INST_ID, shown when querying the GV$SESSION view. This allows you to kill a session on different RAC node.
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';
The KILL SESSION command doesn't actually kill the
session. It merely asks the session to kill itself. In some situations,
like waiting for a reply from a remote database or rolling back
transactions, the session will not kill itself immediately and will wait
for the current operation to complete. In these cases the session will
have a status of "marked for kill". It will then be killed as soon as
possible.
In addition to the syntax described above, you can add the IMMEDIATE clause.
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
This does not affect the work performed by the command, but it
returns control back to the current session immediately, rather than
waiting for confirmation of the kill.
If the marked session persists for some time you may consider killing
the process at the operating system level. Before doing this it's worth
checking to see if it is performing a rollback. You can do this by
running this script (
session_undo.sql). If the
USED_UREC
value is decreasing for the session in question you should leave it to
complete the rollback rather than killing the session at the operating
system level.
ALTER SYSTEM DISCONNECT SESSION
The ALTER SYSTEM DISCONNECT SESSION syntax is an alternative method for killing Oracle sessions. Unlike the KILL SESSION command which asks the session to kill itself, the DISCONNECT SESSION
command kills the dedicated server process (or virtual circuit when
using Shared Sever), which is equivalent to killing the server process
from the operating system. The basic syntax is similar to the KILL SESSION command with the addition of the POST_TRANSACTION clause. The SID and SERIAL# values of the relevant session can be substituted into one of the following statements.
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;
The
POST_TRANSACTION clause waits for ongoing transactions to complete before disconnecting the session, while the
IMMEDIATE clause disconnects the session and ongoing transactions are rolled back immediately.
The
POST_TRANSACTION and
IMMEDIATE clauses can be used together, but the documentation states that in this case the
IMMEDIATE
clause is ignored. In addition, the syntax diagram suggests both
clauses are optional, but in reality, one or both must be specified or
you receive an error.
SQL> alter system disconnect session '30,7';
alter system disconnect session '30,7'
*
ERROR at line 1:
ORA-02000: missing POST_TRANSACTION or IMMEDIATE keyword
SQL>
This command means you should never need to switch to the operating
system to kill sessions, which reduces the chances of killing the wrong
process.
ALTER SYSTEM CANCEL SQL (18c+)
The
ALTER SYSTEM CANCEL SQL command was introduced in
Oracle Database 18c to cancel a SQL statement in a session, providing an
alternative to killing a rogue session.
ALTER SYSTEM CANCEL SQL 'SID, SERIAL[, @INST_ID][, SQL_ID]';
The Windows Approach
To kill the session on the Windows operating system, first identify the session, then substitute the relevant
SID and
SPID values into the following command issued from the command line.
C:\> orakill ORACLE_SID spid
The session thread should be killed immediately and all resources released.
The UNIX Approach
Warning: If you are using the Multithreaded Model in Oracle 12c, you should
not attempt to kill operating system processes. To know why, read
this.
To kill the session on UNIX or Linux operating systems, first identify the session, then substitute the relevant
SPID into the following command.
% kill spid
If after a few minutes the process hasn't stopped, terminate the session using the following.
% kill -9 spid
If in doubt check that the
SPID matches the UNIX
PROCESSID shown using.
% ps -ef | grep ora
The session thread should be killed immediately and all resources released.
Identify the Session to be Killed
Killing sessions can be very destructive if you kill the wrong
session, so be very careful when identifying the session to be killed.
If you kill a session belonging to a background process you will cause
an instance crash.
Identify the offending session using the
[G]V$SESSION and
[G]V$PROCESS views as follows.
SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45
SELECT s.inst_id,
s.sid,
s.serial#,
--s.sql_id,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND';
INST_ID SID SERIAL# SPID USERNAME PROGRAM
---------- ------ -------- ---- ---------- ---------------------------------------------
1 30 15 3859 TEST sqlplus@oel5-11gr2.localdomain (TNS V1-V3)
1 23 287 3834 SYS sqlplus@oel5-11gr2.localdomain (TNS V1-V3)
1 40 387 4663 oracle@oel5-11gr2.localdomain (J000)
1 38 125 4665 oracle@oel5-11gr2.localdomain (J001)
SQL>
The
SID and
SERIAL# values of the relevant session can then be substituted into the commands in the previous sections.
