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 ?
There are a couple of things you can do. First, check if they are sessions that might have been using a database link. If so, you have to kill the remote session in order for them to go away.
If that's not the case, you can always kill the process from the OS side. Use something like
set pages 50
col kill_by for a12
col username for a12
col program for a40
col "MACHINE/PID" for a20
set linesize 132
set pages 100
select '''' || s.sid ||','||s.serial# || '''' kill_by,
p.spid server_pid, s.username, s.program,
decode(s.process, NULL, s.machine, s.machine || ' PID: ' || s.process) "MACHINE/PID" ,
from v$session s, v$process p
where s.username is not null
and s.paddr = p.addr
order by to_number(p.spid)
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.
I'm a JOLE(JavaOracleLinuxEnthusiast)
--- Everything was meant to be---
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...