A user’s process will occasionally hang or
spin and lock out other sessions. If the DBA still has access to
the database, she can usually just issue the following query:

select
s.username, s.osuser, s.sid, s.serial#, p.spid
  from v$session s,v$process p
 where s.paddr = p.addr
   and s.username is not null;

This will return the database username, the
operating system username, the session id, the serial number, and
the system process id (SPID). Then, a DBA user can issue the
following (using the SID and SERIAL# information from the previous
query):

ALTER SYSTEM KILL SESSION ‘sid,serial#’;
ALTER SYSTEM KILL SESSION ‘9,203’;

There are two problems with using this
statement. First, any locks or resources allocated to this process
will not be released until the session completely times out.
Second, the query and kill commands require database access. If a
process is running out of control, any database access might be out
of the question.

On a UNIX database, the next step is to locate
the process from the UNIX prompt using the ps command (while looking
for the OSUSER and SPID ids) and killing the runaway background
process with kill -9
spid
. However, on Windows, there’s only one
process–ORACLE.EXE–and user connections are handled on Windows
threads, not processes. If you use the Windows Task Manager to kill
the Oracle thread, it would bring down all users and background
threads and crash the database.

For these reasons, Oracle provides the command
orakill.exe in the Oracle
Home/bin directory, which has the same arguments as ALTER SYSTEM
KILL SESSION but doesn’t require a database connection. To locate a
particular thread, you need to find a program that displays the
threads of a process. The Windows Task Manager will only show you
the process and the number of threads. You need to find a utility
program, such as the free
QuickSlice
, or Qslice.exe (which is Windows based), or
PStat
(Pstat.exe is command-line based) from Microsoft’s
Resource Kit for Windows 2000 and NT. Simply enter the thread-id
(in decimal) in the orakill command with the following SID:

orakill <sid> <spid>
orakill ORCL 2760

“Kill of thread id 2760 in instance ORCL successfully
signalled[sic].”

You should only use orakill in cases where
access to the database to execute ALTER SYSTEM KILL SESSION isn’t
possible. If you accidentally kill one of the required background
processes, such as PMON, you’ll probably crash the database. This
should never be your first course of action.

TechRepublic’s Oracle newsletter covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more. Automatically subscribe today!