Managing Oracle Sessions: Find and Kill Sessions Using OS Process ID (SPID)

In Oracle databases, each session running in the database corresponds to an Operating System (OS) process. If a session is consuming high CPU, memory, or blocking other sessions, you may need to find and terminate it.

This document provides detailed queries to:
✔ Find database session details using OS Process ID (SPID)
✔ Generate an ALTER SYSTEM KILL SESSION command for RAC & Non-RAC
✔ Kill a session directly from the OS if needed

🔹 Finding Oracle Database Session Using OS Process ID

Use the following SQL query to fetch session details using the SPID (OS Process ID).

🔹 Query for RAC (Multi-Instance Environment)


COL inst_id FORMAT 99
COL sid FORMAT 9999
COL serial# FORMAT 99999
COL username FORMAT A20
COL osuser FORMAT A20
COL machine FORMAT A25
COL program FORMAT A40
COL module FORMAT A30
COL status FORMAT A10
COL sql_id FORMAT A15
COL event FORMAT A30
COL os_process_id FORMAT 99999999
COL kill_command FORMAT A80

SELECT 
    s.inst_id,              -- Instance ID (for RAC)
    s.sid,                  -- Session ID
    s.serial#,              -- Serial Number
    s.username,             -- Oracle DB User
    s.osuser,               -- OS User
    s.machine,              -- Machine Name
    s.program,              -- Running Program
    s.module,               -- Application Module
    s.status,               -- Session Status
    s.sql_id,               -- SQL ID (if active)
    s.event,                -- Wait Event (if any)
    p.spid AS os_process_id, -- OS Process ID
    'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# || 
    ',@' || s.inst_id || ''' IMMEDIATE;' AS kill_command
FROM gv$session s
JOIN gv$process p ON s.paddr = p.addr AND s.inst_id = p.inst_id
WHERE p.spid = '&OS_PROCESS_ID';

🔹 Query for Single-Instance Database


SELECT 
    s.sid,
    s.serial#,
    s.username,
    s.osuser,
    s.machine,
    s.program,
    s.module,
    s.status,
    s.sql_id,
    s.event,
    p.spid AS os_process_id,
    'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# 
|| ''' IMMEDIATE;'  AS kill_command
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
WHERE p.spid = '&OS_PROCESS_ID';

🔹 Understanding the Query Output

Column	Description
`inst_id`	Instance ID (for RAC databases)
`sid`	Oracle Session ID
`serial#`	Serial number of the session
`username`	Database user associated with the session
`osuser`	OS user running the session
`machine`	Host machine name
`program`	Application or tool running the session
`module`	Module name of the running query
`status`	Session status (ACTIVE/INACTIVE)
`sql_id`	SQL ID of the executing SQL statement
`event`	Wait event (if the session is waiting on a resource)
`os_process_id`	OS Process ID (SPID) of the session
`kill_command`	Generated SQL command to kill the session

🔹 How to Get OS Process ID (SPID)

If you do not know the OS process ID, you can list all active sessions:


SELECT p.spid, s.sid, s.serial#, s.username, s.program 
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
WHERE s.username IS NOT NULL;

You can also get the top CPU-consuming sessions:


SELECT p.spid, s.sid, s.serial#, s.username, s.program, 
t.value/100 as cpu_usage_sec
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
JOIN v$sesstat t ON s.sid = t.sid
JOIN v$statname n ON t.statistic# = n.statistic#
WHERE n.name = 'CPU used by this session'
ORDER BY cpu_usage_sec DESC;

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment

Managing Oracle Sessions: Find and Kill Sessions Using OS Process ID (SPID)