Re: Zombie sessions

I've just been through the same thing. sqlnet.expire_time and automatic server process cleanup just doesn't seem to work.

With respect to your nightly shutdown, you could use "shutdown abort", then restart the database in restricted mode and do a "shutdown normal" so your data files are closed in a consistent state.

With respect to orphan processes holding locks and blocking other sessions, my solution was to write a package that checks all processes every so often, and kills (using "alter system kill session") those that meet the following criteria:

1. Hold one or more DML locks
2. Not waiting for a DML lock.
3. No resource usage.

You can get the lock info from v$lock, and the resource usage info from v$sesstat and v$statname. It took me a couple days of observation and experimentation to find the right query to determine which processes met the criteria, but it's relatively straightforward to implement.

If you run into trouble, let me know and I'll see if I can't get you the code. Received on Wed Oct 11 1995 - 00:00:00 CET