How to kill Oracle sessions

There are few ways to kill blocking or hanging Oracle sessions.

But beware when you are doing this, because a small mistake in SID or PID number can bring down entire database( if you kill some Oracle background process for example).

So first we have to identify session that we want to kill.

This can be done from the Enterprise Manager or from the terminal.

select s.sid, s.serial#, p.spid, s.osuser, s.program
from v$process p,
v$session s
where p.addr = s.paddr;

If you know problematic SID, you can add AND clause to this query and get all the data you need.

Alter system kill session

Basic syntax is

alter system kill session ‘sid, serial#’;

When you mark session to be killed, any active transaction is rolled back, so if you had a lot of data changes that are not commited, undo work must be done before session is really killed. And this can take a while.

alter system kill session ‘sid, serial#’ immediate;

From Oracle documentation

Specify IMMEDIATE to instruct Oracle Database to roll back ongoing transactions, release all session locks, recover the entire session state, and return control to you immediately.

Alter system disconnect session

Basic syntax is

alter system disconnect session ‘sid, serial#’ post_transaction;

alter system disconnect session ‘sid, serial#’ immediate;

This command kills server process.

From Oracle documentation.

The POST_TRANSACTION setting allows ongoing transactions to complete before the session is disconnected. If the session has no ongoing transactions, then this clause has the same effect described for as KILLSESSION.

The IMMEDIATE setting disconnects the session and recovers the entire session state immediately, without waiting for ongoing transactions to complete.

Linux way

From the terminal with the kill command.

kill spid;

kill -9 spid;

%:kill 235

Windows way

From the terminal with the orakill command.

orakill instanceName spid;

C:\orakill myoradb 235;