Re: Killed database sessions

Nevin Hahn wrote:

> Ernst,
>
> Indeed PMON is the culprit here. It is supposed to clean up killed
> sessions as well as monitor things like inactive sessions that exceed
> idle time settings or elapsed time settings. However because of the
> performance hit Oracle does not have PMON monitor processes constantly
> and it is unclear how often it is actually activated to remove these
> killed sessions. The manual says minutes but I have experienced killed
> sessions still showing in V$SESSION for hours or days. I agree this is
> frustrating. Since I am not a 7X24 shop I can do a shutdown abort to kill
> the sessions, but I feel for those who are. I too would be interested to
> hear if there is another solution!!!
>
> Nevin Hahn

Nevin,

I think what is actually happening is that the session is getting flagged with a status of KILLED in V$SESSION and, because of that status, Oracle will not clean up the shadow process, etc. until it is able to deliver a message to the user informing him or her that the session has been killed. The "ORA-00028 Your session has been killed" message cannot be delivered until the user actually attempts to access the database again. When you use the alter system kill session command, the session resources are NOT going to be freed until the ORA-00028 is delivered, and that has nothing to do PMON, but rather the internal design of Oracle itself.

If you really want to get rid of users and have the session resources freed, you should listen to what Manoj Appully is saying and kill the operating system shadow process. Manoj and I looked into this matter quite a bit recently. I actually implemented a PRO*C program to take care of the probelm of terminating users who were idle for longer that a certain threshold value of minutes, thus freeing the system resources, reducing the number of concurrent licenses that we must purchase, and improving the security of the system. (Security is a major concern for us since we are deployed to approx. 2000 desktops and our system issues BILLIONS of dollars of payments each month!)

Thanks,

Lamar Spells Received on Thu Mar 06 1997 - 00:00:00 CST