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 :