Killing Oracle sessions

One of the most common problems in Oracle is someone taking huge resources causing the system to appear "hang". Killing an Oracle session can be done either by using Oracle Enterprise Manager(OEM) or SQLPLUS. Killing from OEM is straightforward. However there are times there you couldn't even get into the OEM due to CPU is 100%.

You can either kill it from the Oracle level or Operation system level.

Using the Oracle level method
Identify the session and kill the session using
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';

Using the Operating system level method
In SQLPLUS (Windows)
SELECT s.sid,
p.spid,
s.osuser,
s.program
FROM v$process p,
v$session sWHERE p.addr = s.paddr;

The SID and SPID values of the relevant session can then be substituted into the following command issued from the command line:
C:> orakill ORACLE_SID spid

In SQLPLUS (Unix, Linux)
To kill the session via the UNIX operating system, first identify the session in the same way as the NT approach, then substitute the relevant SPID into the following command:
% kill -9 spid

You can also disconnect the session using these command
alter system disconnect session 'sid,serial#' immediate;