This article describes in short how to kill a session in Oracle Database
Generally there are two methods to kill a session in Oracle Database:
- do it directly in the database
- do it on OS level – kill dedicated server process for the database
Before you kill a session you need to scan following views to find sid, serial#, inst_id for the session
- V$SESSION – used for non RAC databases to find sid, serial#
- GV$SESSION – used for RAC databases to find sid, serial#, inst_id
- SID – session identifier
- SERIAL# – Session serial number. Used to uniquely identify a session’s objects. Guarantees that session-level commands are applied to the correct session objects if the session ends and another session begins with the same session ID
- INST_ID – instance number. Required only for RAC.
--non RAC databases SELECT s.sid, s.serial# FROM v$session s WHERE username = 'SCOTT'; --RAC databases SELECT s.inst_id, s.sid, s.serial# FROM gv$session s WHERE username = 'SCOTT';
To kill a session outside database scan following views to find dedicated server process identifier spid on OS level
- V$PROCESS – non RAC databases to find spid
- GV$PROCESS – RAC databases to find spid
--non RAC databases SELECT s.sid, s.serial#, p.spid FROM v$session s, v$process p WHERE s.paddr = p.addr AND username = 'SCOTT'; --RAC databases SELECT s.inst_id, s.sid, s.serial#, p.spid FROM gv$session s, gv$process p WHERE s.paddr = p.addr AND s.inst_id = p.inst_id AND username = 'SCOTT';
Kill session from database level
Following methods use sid, serial#, inst_id from view [G]V$SESSION.
1. First method is to use ALTER SYSTEM KILL SESSION
ALTER SYSTEM KILL SESSION 'SID, SERIAL#, @INSTANCE_ID' [IMMEDIATE]
Above command marks a session as terminated, roll back ongoing transactions, release all session locks, and partially recover session resources. If this activity is going on it can hang for a minute. To avoid this hang use IMMEDIATE.
Marked session will be killed by Oracle as soon as possible however sometimes it requires to kill dedicated process manually.
--non rac kill example ALTER SYSTEM KILL SESSION '123,34216'; ALTER SYSTEM KILL SESSION '123,34216' IMMEDIATE; --rac kill example ALTER SYSTEM KILL SESSION '123,34216,@2'; ALTER SYSTEM KILL SESSION '123,34216,@1' IMMEDIATE;
2. Second method is to use ALTER SYSTEM DISCONNECT SESSION
ALTER SYSTEM DISCONNECT SESSION 'SID, SERIAL#' POST_TRANSACTION | IMMEDIATE;
This is more effective than ALTER SYSTEM KILL SESSION because it disconnect the current session by destroying the dedicated server process (or virtual circuit if the connection was made by way of a Shared Sever). It must be run on instance where you want to kill Oracle session.
- POST_TRANSACTION – waits for current transaction to complete, then kill dedicated process
- IMMEDIATE – kills immediately dedicated process
ALTER SYSTEM DISCONNECT SESSION '123,34213' POST_TRANSACTION; ALTER SYSTEM DISCONNECT SESSION '123,34213' IMMEDIATE;
Kill session from os level
Other option to kill session is to kill dedicated process on OS. It will give the same very quick effect as ALTER SYSTEM DISCONNECT SESSION. SPID can be found in [G]V$PROCESS view.
--windows c:\orakill ORACLE_SID spid --unix kill -9 spid
It’s worth on Unix to check if the process really exists before executing kill -9
ps -ef | grep spid
Have a fun 🙂