circumstances to kill a session in oracle (en)

In certain scenarios, you might need to kill a running session in Oracle Database. This could be due to long-running locks, high resource consumption, or programming issues. In this article, we'll discuss the important considerations and steps for killing a session or program in Oracle Database, including sessions connected via a shared server.

Important Considerations Before Killing a Session

  1. Check Undo Amount: Before killing a session, check the amount of undo it has generated. If the session has a large amount of undo, killing it might be time-consuming and could impact overall system performance.
  2. Peak Load Time: Verify whether the system is currently experiencing peak load. Killing a session during peak load can slow down the system and negatively affect other users.
  3. Check Connections: Ensure that killing the session won't disrupt critical connections. For example, a session might be executing vital transactions.

Steps to Kill a Session in Oracle Database

  1. Identify the Session

First, identify the session you want to kill. Use the following query:

SELECT sid, serial#, username, program, status

FROM v$session

WHERE status = 'ACTIVE' and ...

;

You have to put some other statistics like machine, program, username and so on to find the exact session which should be killed.

  1. Check Undo

To check the amount of undo for a session, use the following query:

SELECT s.sid, s.serial#, s.username, t.used_urec, t.used_ublk

FROM v$session s

JOIN v$transaction t ON s.saddr = t.ses_addr

WHERE s.sid = :sid;

Based on the result the decission should be taken. If there are lots of undo segment, sometimes it would be better to postpone the killing action.

  1. Kill the Session

To kill a session, use the following command:

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

Killing a Process at the Operating System Level

If the session does not terminate at the database level, you can kill the corresponding process at the operating system level. First, find the PID associated with the session:

SELECT p.spid

FROM v$process p

JOIN v$session s ON p.addr = s.paddr

WHERE s.sid = :sid;

Then, execute the following command in the operating system (Linux/Unix):

kill -9 <PID>

Handling Sessions Connected via Shared Server

In a shared server environment, sessions are handled differently, and killing them requires additional steps.

  1. Identify the Dispatcher Process

First, identify the dispatcher process handling the session:

SELECT s.sid, s.serial#, p.spid as "PID", q.paddr as "Dispatcher PID"

FROM v$session s

JOIN v$queue q ON s.paddr = q.sid

JOIN v$process p ON q.paddr = p.addr

WHERE s.sid = :sid;

  1. Kill the Session

Kill the session using the standard command:

ALTER SYSTEM KILL SESSION 'sid,serial#';

  1. Kill the Dispatcher Process

If the session does not terminate, you may need to kill the dispatcher process at the OS level:

kill -9 <Dispatcher PID>

Be cautious when killing dispatcher processes, as it might affect other sessions connected via the same dispatcher.