Orakill

The orakill utility is provided only with Oracle databases on Windows platforms. The executable (orakill.exe) is available to DBAs to kill Oracle sessions directly from the DOS command line without requiring any connection to the database.

In the UNIX world, a DBA can kill a shadow process by issuing the kill –9 command from the UNIX prompt. UNIX is able to provide this capability given that the UNIX operating system is based on processes that fork other processes. All processes can be listed by using the ps UNIX command. The Oracle background processes will be listed separately from all of the Oracle sessions since they have their own process.

Unlike the UNIX operating system, Windows systems are thread-based. For each instance, the background processes and sessions are all contained within the oracle.exe executable. These processes are not listed in the “Processes” tab of Windows Task Manager. Each session creates its own thread within oracle.exe and therefore, is not exposed to the Windows user. Killing the oracle.exe process in Windows would crash the entire database.

The orakill utility serves the same purpose as kill –9 in UNIX . The command requires the instance and the SPID of the thread to kill. The utility will display exactly how to obtain the SPID in the event the command was entered without parameters:

C:\oracle9i\bin>orakill

Usage: orakill sid thread

where sid = the Oracle instance to target
thread = the thread id of the thread to kill

The thread id should be retrieved from the spid column of a query such as:

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

If the statement suggested by Oracle (above) to retrieve the Thread ID is executed, the results below are displayed:

select a.username, a.osuser, b.spid
from v$session a, v$process b
where a.paddr = b.addr
and a.username is not null;


USERNAME OSUSER SPID
------------------------------ ------------------------------ -----
SCOTT Scott 3116
AMOORE Alex 4760
DMOORE Dave 768

With the SPID for each user listed above, the session for any user can be killed.

C:\oracle9i\bin>orakill ORCL92 4760

Kill of thread id 4760 in instance ORCL92 successfully signalled.


SQL> select a.username, a.osuser, b.spid
2 from v$session a, v$process b
3 where a.paddr = b.addr
4 and a.username is not null;


USERNAME OSUSER SPID
------------------------------ ------------------------------ -----
SCOTT Scott 3116
DMOORE Dave 768

2 rows selected.

Notice that SPID 4760, user AMOORE is gone.

Why does Oracle provide a utility to kill sessions from the DOS prompt, when a DBA could kill a user session from within Oracle? The following command will also kill the user session:

alter system kill session(sid, serial#);

The sid (session ID) and serial# above can be obtained from the v$session view. There are a couple of reasons a DBA might use orakill instead of the alter system kill session command.

1. The alter system statement will not clear any locks that exist. Instead, the session will remain connected until it times out, then the session is killed and the locks are released. The orakill command will kill the thread and the locks instantly.
2. A DBA may be unable to gain access to a SQL prompt due to a runaway query consuming all database resources. In this case, the session can be killed without ever logging in to the database.

If you would like more information on any of Quantix's Oracle product range then please visit the Quantix website or contact us on 0115 983 6200