Re: How to really kill a session

Dead simple - Oracle uses threads (all wrapped up within one process) on NT.

Accordingly, because there are no easily available OS tools that can kill threads, Oracle have created a tool that should allow you to do this.

orakill.exe is the executable you should be looking for.

From the command line, simply run it with the two command line parameters.

Usage: x:\bin\orakill.exe sid thread

where sid = the Oracle instance to target,

      thread = the thread id of the thread to kill (in hex)

if you do :-

SQL> SELECT spid, s.username, s.program, s.status   1 FROM v$process p, v$session s where p.addr=s.paddr;

You'll get the spid's for the sessions (you may want to add another conditional clause to e.g. select a specific username).

N.B. if you ALTER SYSTEM KILL SESSION .. before you run the query above, the spid is no longer available!

The trouble with this is that if the session isn't going away after an ALTER SYSTEM KILL SESSION... then by that time its too late to retrieve the spid and hence its too late to use orakill.

So the best approach may be to run the query above, and get the thread id (spid) of a session whenever you think the session might not be properly removed by ALTER SYSTEM KILL SESSION. Then with luck, if ALTER SYSTEM KILL SESSION doesn't work, you'll have the information you need to run orakill instead.

Better to test this out (especially the last paragraph) on a development instance first if possible.

David P. OCP (DBA) MCP (TCP/IP)

Glasgow, Scotland.

Michael Atkins wrote:
>
> Hi,
>
> Running Oracle 7.3.3, and I need to eliminate sessions left stranded
> by a power outage. I can get them to status "KILLED", but I'm using a
> concurrently licensed program that still counts these sessions.
> Running on an NT box, there are others that I know of that have run
> into the same thing with UNIX installs. Any help is greatly
> appreciated.
>
> TIA,
> Michael
Received on Thu Jun 01 2000 - 00:00:00 CDT