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 asKILL
SESSION
.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;