Re: Killing disconnected Oracle sessions

> >Does anyone know how to detect Oracle sessions which are disconnected
 from
> >their client machines, but which linger on as Oracle sessions?

In the past, when supporting a live system, I have written UNIX scripts using awk, etc to filter the output from SQLDBA's MONITOR display. This was far from ideal, since maybe once a day I would have to:

• run SQLDBA and save the MONITOR display with the two process id's (I forget which one it is) for each user to a file
• run my scripts which pulled out the process id's from the file and then checked to see if the processes still existed (sorry I don't work there any more and don't have my own copies).
• check any candidate process id's manually (I wouldn't trust any program I wrote to do this correctly all the time!)
• phone the Ops to get them to kill the rogue processes (even if I could identify them I wasn't allowed to go around killing processes on their live machine)

but it is possible if you can't find anything better.

BTW. If you've got the nerve, it would be possible to automate the shell scripts, but the sticking point I had was getting the pairs of process id's for each user out of Oracle. I couldn't get SQLDBA to successfully take its input from a file, so I had to run it manually. If you could get this information out of the Data Dictionary or something,  maybe it is possible to automate the whole thing.

Even if you can't automate it, the above actions can help speed up solving the problem after it's happened - of course, this isn't much help if your irate users have just been sitting around waiting for a lock for half an hour!

> >What seems to happen is that every several days, the users begin to
> experience

Do you mean that rogue processes are sometimes around for days ? If so, perhaps you could drop the database each night to get rid of them.

For a real solution for preventing this in the first place, you'll have to wait for a version of SQLNET that handles all of this itself......

--
Malcolm Campbell
Bluebird Systems Ltd
+44 (0)131 225 7758
malcolm_at_bluebird.demon.co.uk