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
- 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.
- 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.
- 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
- 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.
- 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.
- 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.
- 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;
- Kill the Session
Kill the session using the standard command:
ALTER SYSTEM KILL SESSION 'sid,serial#';
- 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.