Killing Oracle Sessions


There are a number of ways to kill rogue sessions both within Oracle and externally.

Identify the Session to be Killed

ALTER SYSTEM KILL SESSION

ALTER SYSTEM DISCONNECT SESSION

The unix approach

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#,
       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 74 4930 1230 SYS oracle@accord (PZ99)
 1 118 172 16619 SYS racgimon@accord (TNS V1-V3)
 1 71 11350 11713 DBSNMP JDBC Thin Client
 1 140 36 16742 SYS racgimon@accord (TNS V1-V3)
 1 115 6 16844 SYS racgimon@accord (TNS V1-V3)
 1 61 5965 5488 DBSNMP JDBC Thin Client
 1 69 55888 14760 RMS_USER JDBC Thin Client
 1 79 47538 23800 SYS sqlplus@accord (TNS V1-V3)
 1 66 19857 15035 RMS_USER JDBC Thin Client
 1 88 5510 9877 RPXWEB_USER
 1 56 9961 4912 RPX_USER

 INST_ID SID SERIAL# SPID USERNAME PROGRAM
---------- ---------- ---------- ------------------------ ------------------------------ ------------------------------
 3 81 38776 5016 DBSNMP JDBC Thin Client
 3 33 47777 30200 SYS oracle@kendall (PZ99)
 3 30 13979 14550 DBSNMP JDBC Thin Client
 3 104 6621 4197 DBSNMP JDBC Thin Client
 3 97 4604 31402 DBSNMP JDBC Thin Client
 3 34 23920 9721 RPX_USER
 3 100 60776 17889 RMS_USER JDBC Thin Client
 3 110 71 30606 SYS racgimon@kendall (TNS V1-V3)
 3 36 8776 25417 RMS_USER JDBC Thin Client
 3 61 30222 7467 RMS_USER JDBC Thin Client
 3 109 154 30660 SYS racgimon@kendall (TNS V1-V3)

The SID and SERIAL# values of the relevant session can then be substituted into the commands in the following sections.

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.

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s