How to Kill a Hanging Oracle Session Safely

 Introduction

Sometimes an Oracle session may hang or consume too many resources, causing performance problems. As a DBA, you may need to safely kill a session without restarting the database.

Step 1: Identify Active Sessions

Connect as SYSDBA:

sqlplus / as sysdba

Check active sessions:

SELECT sid, serial#, username, status, osuser, machine, program

FROM v$session

WHERE status = 'ACTIVE';

Step 2: Find the Problematic Session

Look for:

Sessions running too long

Sessions consuming too much CPU or I/O

Hanging sessions with no activity

Optional: join with SQL text:

SELECT s.sid, s.serial#, q.sql_text

FROM v$session s

JOIN v$sql q ON s.sql_id = q.sql_id

WHERE s.status='ACTIVE';

Step 3: Kill the Session

Use SID and SERIAL# from Step 1:

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Example:

ALTER SYSTEM KILL SESSION '123,456' IMMEDIATE;

Step 4: Verify Session is Removed

Re-run the query:

SELECT sid, serial#, username, status 

FROM v$session 

WHERE status='ACTIVE';

If still exists → the process may need to be killed at OS level.

Step 5: Kill Session at OS Level (if required)

1. Find process ID (SPID):

SELECT p.spid, s.sid, s.serial#, s.username

FROM v$session s, v$process p

WHERE s.paddr = p.addr

AND s.sid = 123;

2. Kill process in Linux:

kill -9 <spid>

Conclusion

Killing sessions should always be a last resort. Before killing, try to identify the cause of the hang. Use this method only when a session blocks critical work or consumes too many resources.