Home -->

 

Technical Documents

 - Java Caps (JCAPs)

    - Architecture

    - Domain vs. Logical Host

    - Web Services

    - JCD vs. BPEL

    - Scalability

    - JVM

 - Java

    - JDBC Stored Proc Calls

 - Oracle

    - HS Connectivity Setup

    - Orakill

    - PL/SQL and JSP

    - Queues

Applications

Links

 - Webservices (Axis)

 - PSOUG (Oracle)

 - JCAPs Forum

 

Blog

 - Enter Blog

 

Have a topic I may know about?  Email me

 

 

How to use Orakill

 

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
orakill ORCL 2760

"Kill of thread id 2760 in instance ORCL successfully signalledsic."

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.