How to Kill Session in Oracle 19c | ALTER SYSTEM KILL SESSION / ALTER SYSTEM DISCONNECT SESSION /ALTER SYSTEM CANCEL SQL

Hey Readers, Welcome to the blog once again, Being an Oracle DBA, you might receive a request for killing a session. For Instance Application developer fired a Update/Delete statement two times and now He want to kill the session or Since pandemic most of us are working from home and due to network fluctuation at user end, there was network disconnection and he fired an SQL which will be updating say 50lakhs record. Now he requesting Oracle dba to kill Oracle Session. These are some of the examples where user can request you to kill their session. Lets understand the ways to Kill Oracle Sessions in oracle 19c and how to identify the session details to kill the oracle session.

How to Identify the session details to Kill the session

To kill the oracle session you require 3 details – SID, SERIAL# and INST_ID . But before that you need to identify the session.

We always be assure we are killing the appropriate session (which is asked for by the application/developer team) in any environment. To identify a session I will ask certain set of questions to end user like- Is there any way your session can be differentiated based on OSUSER/MACHINE/MODULE/SID/SQL_ID

Some End users usually note down their SID and SERIAL# for us. That make our task lot easier. Let me tell you the sql which 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

Basically it’s always better that you know the type of environment you are working on. Is it single instance database or multiple instance database (RAC). It’s important for you to identified the type of the database, as Killing a session has different syntax on RAC compared to single instance database.

Single instance Syntax –

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

Immediate is required if you want to kill the session immediately and don’t want Oracle to complete the current operation to complete.

RAC database Syntax –

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

Since RAC database usually have more than one instance, we need to tell the system I want to kill the session from that specific instance using inst_id

If you don’t pass inst_id on RAC system, then you will end up killing a session on which you connected with privileged user. ( Session may exists or may not exists but still better to give the inst_id )

When you kill a session – And end user try to fire a sql on it.

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

ALTER SYSTEM DISCONNECT SESSION

It does the similar thing by destroying the dedicated server process ( or virtual circuit if the connection was made by way of a Shared Server)

Alert log clearly suggest how you tried to kill the session –

2021-08-24T10:03:05.171436+04:00
KILL SESSION for sid=(283, 26592):
  Reason = alter system disconnect session
  Mode = KILL SOFT DISCONNECT/POST_TXN/-
  Requestor = USER (orapid = 49, ospid = 13731, inst = 1)
  Owner = Process: USER (orapid = 61, ospid = 14489)
  Result = ORA-0
2021-08-24T10:03:11.414459+04:00

When you disconnect a session – And end user try to fire a sql on it.

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

POST_TRANSACTION – Allows session to complete the current transaction before the session is disconnected.

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

IMMEDIATE – disconnects the session , recover the entire session state without waiting for on going transaction.

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

ALTER SYSTEM CANCEL SQL

This is the new feature available from oracle 18c where you can cancel the SQL running on the session without killing the session.

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

Reference –
Oracle Official Link on How to Kill Oracle Session
Syntax of Alter system in Oracle19c