How to Kill Session in Oracle 19c

As an Oracle DBA, there may be times when you need to terminate a session.

For example, an application developer might accidentally run a resource-intensive query multiple times or face network issues during a large update operation. In such scenarios, they might ask the DBA to kill the session to avoid further impact on the database.

How to Identify the session details to kill the session?

To terminate an Oracle session, you need three key details: SID, SERIAL#, and INST_ID. However, it is essential to accurately identify the session before proceeding with termination.

To ensure we’re targeting the correct session, I typically ask the end user a series of questions to gather relevant information. For instance, I inquire whether their session can be identified based on parameters such as OSUSER, MACHINE, MODULE, SID, or SQL_ID.

Some end users proactively provide their SID and SERIAL# details, which simplifies the identification process. Here is the SQL query I use to identify the session:

SELECT s.inst_id,s.sid,s.serial#,s.sql_id,p.spid,s.event,s.username,s.program,osuser from gv$session s join gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id where s.username is not null and s.type <> 'BACKGROUND'
---and status='ACTIVE'
---and osuser='&osuser'
---and username='&user'
---and machine='&machine'
---and program like '%<pattern%'
/

ALTER SYSTEM KILL SESSION

Understanding the type of environment you are working with is crucial, whether it is a single-instance database or a multiple-instance database (RAC). This distinction is important because the syntax for terminating a session differs between RAC and single-instance databases.

Single instance Syntax –

SQL> ALTER SYSTEM KILL SESSION '<sid>,<serial#>' <immediate>;

Using “IMMEDIATE” is necessary if you want to terminate the session immediately without waiting for Oracle to complete its current operation.

RAC database Syntax –

SQL> ALTER SYSTEM KILL SESSION '<sid>,<serial#>,@<inst_id>' <immediate>;

In RAC databases, where multiple instances are involved, it is essential to specify the “inst_id” to indicate from which specific instance you want to terminate the session.

Omitting the “inst_id” parameter may result in terminating a session on the instance to which you are currently connected as a privileged user. To ensure the session is terminated on the intended instance, always include the “inst_id” parameter.

If you terminate a session, and the end user tries to execute a SQL command on it, they will encounter an error.

SQL> select count(2) from oracledb1;
*
ERROR at line 1:
ORA-00028: your session has been killed

ALTER SYSTEM DISCONNECT SESSION

It achieves a similar outcome by terminating the dedicated server process or virtual circuit if the connection was established through a Shared Server.

Post Transaction – Enables the session to finish the ongoing transaction before disconnecting the session.

ALTER SYSTEM DISCONNECT SESSION '<sid>,<serial#>' POST_TRANSACTION;

Immediate – Disconnects the session and recovers the entire session state without waiting for ongoing transactions to complete.

ALTER SYSTEM DISCONNECT SESSION '<sid>,<serial#>' IMMEDIATE;

ALTER SYSTEM CANCEL SQL

This is a new feature available from Oracle 18c where you can cancel the SQL running on the session without terminating the session itself.

ALTER SYSTEM CANCEL SQL 'SID, SERIAL[, @INST_ID][, SQL_ID]';