Dealing with "orphaned" sessions

Subject: Dealing with "orphaned" sessions

Platform: Oracle 8.0.5, Std. Ed.; Windows NT 4.0, sp6

Every Saturday evening we have a short maintenance window, during which we have the servers hosting our Oracle db's rebooted. Most of the servers host multiple Oracle databases. I have created a command file that the operators execute before cycling the server. One of the things this file does is execute a SQL file to log on to each database and issue a SHUTDOWN IMMEDIATE.

For several weeks, this process seems to hang at one particular database. The logs indicate that the connection is successful and the SHUTDOWN is issued, but up to an hour later we are still waiting for the shutdown to complete. Due to time constraints we eventually have to pull the plug and depend on crash recovery when we restart.

Looking at V$SESSIONS and V$SESSION_WAIT, I see an inordinate number of sessions on this database. The application only runs on 4 walk-up touch-screen kiosks. Those and a couple of admin machines are all the connections the db should be getting, yet there will be well over 100 sessions. Further investigation of V$SESSION_WAIT shows that the vast majority of these sessions are waiting for "SQL*Net message from client" with SECONDS_IN_WAIT ranging up to several days! The nature of the application allows users to walk away, but the application developer (with whom I have a very good working relationship, and trust to be shooting straight) insists the app checks this and issues a disconnect after a few minutes of inactivity.

Question #1: Am I mis-interpreting V$SESSION_WAIT when I assume that a session showing "SQL*Net message from client" and SECONDS_IN_WAIT of 179909 (49.97 hours) probably represents a session that didn't get disconnected and someone initiated a new session from the same machine?

Question #2: Would waiting for SHUTDOWN IMMEDIATE to roll these back be causing my shutdown problems? If not, what else should I be looking for? If so . . .

Question #3: If I were to write a script to detect these and terminate them, would that not generate the same rollback delays as simply issuing a SHUTDOWN IMMEDIATE.

--
Ed Stevens
(Opinions expressed do not necessarily represent those of my employer.)