While digging into Oracle 10gR2 Backup and Recovery Advanced User’s Guide document I came across a nice utility called orakill. This utility can be called “kill -9 of Windows for Oracle”. As you know Unix operating systems based on processes that fork another processes and you can see all of the working Oracle processes by using “ps -ef”, unlike unix, windows is thread based and Oracle can be seen working as only one process called oracle.exe. If you want to see the threads you must your programs like process explorer. If you are in a position to kill an oracle thread from OS on windows you can use orakill utility instead of killing oracle.exe
You might ask why should I use orakill ? The answer is given by http://www.oracleutilities.com.
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. (Check out the comments of Yasin about this case )
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.
You can use the syntax below when you are in a situation like above.
Thread number can be obtained by SPID column of v$process table. If you are unable to query this tables you can use QuickSlice from Windows.
Here is quick demo to show how to use orakill utility.
It is interesting that it is only mentioned in 1 book of Online documentation ???
References Used :
Oracle® Database Backup and Recovery Advanced User’s Guide
10g Release 2 (10.2)
Coskan's Approach to Oracle 
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.
That is not true. “alter system kill session” releases the locks held by the killed session.
SQL> create table test as select * from all_objects;
Table created.
SQL> select sid from v$mystat where rownum=1;
SID
———-
341
SQL> select sid,serial# from v$session where sid=341;
SID SERIAL#
———- ———-
341 21620
SQL> update test set object_id=1;
61657 rows updated.
From another session:
SQL> select sid,type from v$lock where sid=341;
SID TY
———- —
341 TX
341 TM
SQL> alter system kill session ‘341,21620’;
System altered.
SQL> select sid,type from v$lock where sid=341;
no rows selected
The related documentation also confirms that : http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_2013.htm#i2065117
Comment by Yas — August 28, 2007 @ 8:05 am
Thanks for the enlightening comment Yasin
As I mentioned this were the web site owners thoughts and The web site owner might mean if sometimes, not every time.
I think they want to mean, There can be a possibility of a hanged session which stays there after killed. like marked as killed for rolling back.
Also test below shows us that alter system kill session could not immediately release the locks
—User Session
11:49:48 USER_SESSION>select sid from v$mystat where rownum=1;
SID
———-
157
11:49:51 USER_SESSION>SELECT sid,serial#,username,status from v$session where sid=157;
SID SERIAL# USERNAME STATUS
———- ———- —————————— ——–
157 710 SYS ACTIVE
11:54:04 USER_SESSION>insert into t1 select * from dba_objects union all select * from t1;
insert into t1 select * from dba_objects union all select * from t1
*
ERROR at line 1:
ORA-00028: your session has been killed
—-Admin session
11:49:25 ADMIN_SESSION>alter system kill session ‘157,710’;
System altered.
11:54:27 ADMIN_SESSION>SELECT sid,serial#,username,status from v$session where sid=157;
SID SERIAL# USERNAME STATUS
———- ———- —————————— ——–
157 712 ACTIVE
11:54:52 ADMIN_SESSION>select sid,type from v$lock where sid=157;
SID TY
———- —
157 PS
11:55:10 ADMIN_SESSION>select sid,type from v$lock where sid=157;
no rows selected
Session is killed at 11:54:04 but lock still held for rolling back purposes at 11:54:52
Anyway, this tool is cool for the ones who are in trouble and have nothing to do.
Comment by coskan — August 28, 2007 @ 8:23 am
Don’t try this with compiled PL/SQL code. PL/SQL in Oracle 9 and 10 both get upset if you try.
The only way to do this is to call Java – look in asktom for the article covering this, and be very carefull to pay attention to the Java security part.
Comment by CliffP — August 28, 2007 @ 1:54 pm
Try What ?
Killing a session running compiled PL-SQL by orakill ?
Comment by coskan — August 28, 2007 @ 2:03 pm
I would say two things:
1) orakill does not work as kill -9, i’ve a windows 2003 standard edition sp1 machine with Oracle 10.2.0.2 used as development server. when i make orakill SID SPID it gives me the success message, but thread still remains. With kill -9 this never appens
2) Sessions never times out, if client does not cleanly close the connection session persist forever (until the instance is not stopped)
On this server i’ve the problem that developers with debuggers don’t close cleanly oracle sessions so they remain there for days , new sessions are opened and so number of processes grows until maximum processes number is reached. I’ve tried with profiles and idle_time setting but sessions get status “SNIPED” and that does not resolve. With DCD (SQLNET.EXPIRE_TIME) sessions get closed, but shadow processes still remains so my problem remains. Killing threads with Process Explorer works fine, so i’m searching a command line tool to kill thread that works in the same manner to schedule a batch to clean deadh sessions.
Comment by cristiancudizio — November 7, 2007 @ 10:59 am
Thanks for that info.
I think that I had the same problem, in that orakill returned success but the thread was still running.
I think that Oracle has lost track of it’s thread somehow. I couldn’t see it in v$process.
Anyways… after debating with myself for a while, I killed it using Process Explorer. This appears to have worked. At least… there’s been no immediate problem.
Comment by RichardG — July 3, 2008 @ 6:36 am
I have problem using orakill too. Every time it said successfully signaled. But the session is still there and I ended up reboot the box since this thread generate a huge trace file which I can’t delete. By the way what is “Process Explorer”?
Thanks,
Comment by Shirley S — February 27, 2009 @ 4:25 pm
This tutorial is confusing if one doesn’t know what an oracle instance id is. For the newbies out there like me this might be worth explaining.
Comment by John — January 12, 2011 @ 4:24 am
SID of the db is basically the output of the query below.
select instance_name from v$instance
Comment by coskan — January 12, 2011 @ 11:42 pm