Printable View
Hi,
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 ?
Environment:
Solaris 5.8
Oracle 8172
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 liketo find the OS Process id. Then from your OS, issue kill -HUPCode: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" ,
s.server, s.status
from v$session s, v$process p
where s.username is not null
and s.paddr = p.addr
order by to_number(p.spid)
. 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.
Jeff,
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...
Any ideas ?
Hmmm, I had a problem like this but using connection managers. The client process would go away but still would show up in v$session. We just bounced the cman to clear everything up.
Are you using MTS or dedicated servers? If MTS, you could down one of the dispatchers, but that would also blow out anybody that has a valid connection.
It is possible that the killed session is not doing any processing currently, in that case the query might not
list it. Why dont you just kill the process from OS??
Jeff,
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...
Reddy,
I am interested to know the final soultion , which you have given to this problem, cause i ahve seen this manay times and i end up bouncing the DB.
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.
Cheers!
Tarry
Thats not possible because its another co-dba laptop and he shutdown and start it everyday whenever he wants to use. Iam thinking of open a tar and see whats oracle solution for this.Quote:
Originally posted by Tarry
the process kept running on the client machine even though the application was closed.
Tarry
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...
[Edited by sreddy on 06-13-2002 at 11:54 AM]
Do you have SQLNET_EXPIRE_TIME set for your environment ? If yes, this would not have happened...
I do not want to enable that parameter just because of weblogic application server connection pooling. I wanted to confirm whether DCD (dead connection detection) meachanism applies to only abnormally terminated connections or even for inactive sessions sitting idle for more than 24 hours say...
No technical document say anything about inactive sessions.
If it cleans up connections from weblogic connection pooling... I have to pack up and post my resume on web for new job.
[Edited by sreddy on 06-13-2002 at 02:05 PM]
Whatever I know @ this parameter, setting it up will not make you write you resume.
Before terminating the session based on this parameter, Oracle will see if the client is responding. And if that's true, the session is left alone.
But you can always test it out for your specific scenario...
I meant, except the regular user sessions rest of all the sessions(application server, informatica) will stay for hours/days in inactive status accessing database now and then... and these sessions are critical for business and time taking to repeat the task if it breaks like loading data etc.,
By any chance anybody has seen the results with this parameter ?
Seems docs says DCD applies to only connections abnormally terminated. I can double check with oracle / Test the same and post the updates.