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?

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 Received on Fri Mar 17 1995 - 18:04:19 CET