If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

I killed few user sessions couple of weeks ago, Oracle show the session status as 'Killed' and never release those processes. I can't see session process id (spid) if I run the query against v$session and v$process. Whats going on here? could somebody explain ?

Bouncing database is not an option for me to clean held up processes and kinda resources are limited on these box so some how I need to clean these processes. How ?

to find the OS Process id. Then from your OS, issue kill -HUP . If your process doesn't die in a few minutes, issue kill -KILL . It might take a couple of minutes for the entry to be removed from v$session.

Thanks much for input. Those processes are not dblinks are something but, another dba laptop sessions which were held up from first week of April which I killed sometime in May. Last night we got error of Max processes exceeded. Today I see those processes sitting in Killed status. Its Weird. I ran the query which show up session processes and it doesn't show up any processes held from that machine/user. Even the query you gave me doesn't list those processes.

To my understanding there is no process held up from OS side for that user but oracle side there is something goofy going on...

Its not MTS enabled. The total number of processes held by Oracle from couple of weeks are 22. Resources are limited to only 200 processes. Now and then Iam hitting Max Processes exceeded...

I'm thinking of now! even if I go and kill the low priority user sessions to give priority for Application sessions Iam not gonna buy anything as Oracle never release those processes. So apparently killing current processes doesn't help much either...

It's a very common and weird sight but if users are using different tools to access the db, even after they are killed or after being killed and asked to login to finally complete the killing (since the session was inactive) by executing and allowing it to complete.And then closing the application. Still I've found very weird(I got into an argument with the SA i remember because he thought I was messing around) that the process kept running on the client machine even though the application was closed.
Maybe this will throw in some light and help you further.

This is what Oracle explaination for killed sessions not being removed from v$session view and holding the session process and getting ORA-00020 Max processes exceeded error (when you have limited processes that you can configure on the server).

When an inactive session has been terminated, STATUS in the V$SESSION view is KILLED. The row for the terminated session is removed from V$SESSION after the user attempts to use the session again and receives the ORA-00028 message.

PMON will not delete the session object itself until the client connected to that session notices that it has been killed Therefore, the sequence of events is:

1) alter system kill session is issued - the STATUS of the session object in V$SESSION becomes KILLED, its server becomes PSEUDO.
2) PMON cleans up the *resources* allocated to the session (i.e., rolls back its transaction, releases its locks, etc).
3) the entry in V$SESSION remains there until the client of that session tries to do another request.
(the client is the process associated with the OSUSER,MACHINE,PROCESS columns in the V$SESSION view)
4) the client attempts another SQL statement and gets back ORA-28.
5) PMON can now remove the entry from V$SESSION.

Oracle says: this behavior is necessary because the client still has pointers to the session object even though the session has been killed. Therefore, the object cannot be deleted until the client is no longer pointing at it.

The onlyway to get rid of these killed sessions with holding resources is by BOUNCING THE DATABSE. No one thinks killing sessions (not couple, but many) might get the situation of bouncing the database... That's the way Oracle works...