In Windows, there are a couple of kill utilities: the
utility and the Windows
taskkillprogram. If the
session cannot be killed more gracefully via alter system kill session, or
the instance is inaccessible via SQL, then
orakill should be used to
terminate the offending session.
Access to the Windows machine containing the database
must be secure to use orakill. Any
user with access to the box could access
orakill or the Windows Task Manager and damage database processes.
Oracle on Windows is implemented based upon threads
rather than processes. So when the Windows Task Manager is viewed, all that
will be seen is one ORACLE.EXE for that database instance. The individual
threads for the sessions will not be visible since Task Manager only shows
the process and number of threads. Look at the next screen snapshot; there
are two SQL*Plus sessions connected to the database on this Windows box.
Even though two sqlplus.exe processes can be
seen, there is but one oracle.exe process. One would need to dig
deeper than the basic Windows task management program to find the Oracle
dedicated database server process’ thread.
ORACLE.EXE in Windows Task Manager
The DBA could use a utility program, such as the free
and PStat(pstat.exe) from the
Microsoft's Resource Kit, or Process Explorer
also from Microsoft.
With tools like these, open the oracle.exe
process and investigate into its many threads. However, if one simply
selects the 1176 thread using Process Explorer and presses the KILL button,
there could be a problem. These tools do not inform Oracle’s PMON as to
what just occurred, so they do not always make a hanging session and its
locks go away.
It still may be necessary to also manually run
oradebugwakeup 1 to clear the locks,
v$sessionand v$process. Hence, it is advisable to always use the Oracle-
provided orakillutility instead. Just be careful, because if the wrong
thread, such as a background process, is chosen, then the entire database
Selecting 1176 Thread in Process
The Windows command to kill this session with orakill
would be as follows:
In this example, the Windows thread corresponding to the
Oracle session can be killed in the operating system without ever logging
into the database. For another example, the Windows command to kill a
session would be:
In this example, the thread (Oracle session) was killed
in the operating system without ever logging into the database. Before
killing the session, the DBA may decide to view the SQL being executed by
the session. This can be obtained by using the TID above (300) in the
following SQL statement:
v$sqltext_with_newlines a, v$session b, v$process c
c.spid = to_number('300', 'xxx')
c.addr = b.paddr
b.sql_address = a.address;
The orakillcommand is very simple. In fact, it is essentially the
exact same syntax as the ALTER SYSTEM KILL SESSION. This means that it
accepts two parameters – the SID and
– which represents the thread number.
C:\Temp>orakill OR0310 1176
Kill of thread id 1176 in instance OR0310 successfully
This is an excerpt from
the book "Oracle
Utilities", and you
an get it directly from
the publisher for
30%-off at the link.