Re: Killing disconnected Oracle sessions

In <rblizard.4.0219855C_at_bb.ultranet.com> rblizard_at_bb.ultranet.com (Bob Blizard) writes:

>
>Does anyone know how to detect Oracle sessions which are disconnected
 from
>their client machines, but which linger on as Oracle sessions?
>
>I have a client who is experiencing problems with these lost sessions
 locking
>up other users. Specifically, they have Oracle 7.0.16 on hp9000,
 orasrv, and
>clients running a PowerBuilder app (I didn't write and can't see under
 the
>hood). The application has everyone logging into Oracle under 1
 account, and
>inserting/updating 15-20 tables. There are approx 20 users of the
 database.
>
>What seems to happen is that every several days, the users begin to
 experience
>'hanging' problems, which turn out to be waits for locks. PB is not
 returning
>any kind of lock delay error. One user will hold a 'TX' lock on an
 object in
>mode 'X', with others waiting for the same thing. Turns out that the
 session
>is inactive, and orasrv log shows that the same ip address logged into
 the
>database a second time an hour later than the unix process that is
 holding the
>lock.
>
>So, ... there are two issues here.
>1. Does oracle issue a warning if the lock is being held too long, or
 too many
>lock waiters?
>2. Is there a way to make Oracle or unix detect (and kill or report)
 these
>disconnected sessions?
>
>I was considering limiting connect time via a profile, but the users
 can
>legitimately stay on all day, so that doesn't seem to make much sense.
>
>All ideas accepted.
>
>Bob Blizard
>

We have a similar problem on IBM AIX box and have been told by Oracle tech support that going to SQL*Net 2.1 will resolve some of the problems with "dead connections" as SQL*Net 2.1 does a better job of "cleaning up" after itself. I would be interested to hear from you if
you are running SQL*Net 2.1 and have these problems. Received on Sun Mar 19 1995 - 22:15:34 CET