Dean said...Fantastic hint ! Note that on Oracle 7, the ALTER SYSTEM KILL ... IMMEDIATE option doesn't seem to exist. But who uses Oracle 7 anyway right?? (Only my stupid client..!!)
Tariq said...Thanks, it help a lot.
Vinod said...Thanks !!!!!
It helped a lot.
Michael said...In Unix you should always first try a "smooth" "kill pid" and wait some seconds before hard killing "kill -9 pid" a process.
The smooth kill (aka SIGTERM) give a process the chance to clean things up before terminating, whereas the hard kill doesn't.
Sure, the hard kill alway terminates the process (except it is hanging in "kernel-space"), but if a process has open file,semaphores,etc. they won't be cleaned up.
Joe said...I completely agree with what Michael said.
Tim... said...Point taken. Article amended. :)
Damnit said...Hey, you should inform that kill -9 spid terminate all oracle process and shut it down .. because of you i'm on trouble !! .. so i'm not going to say thanks to you ..
If you don't understand that killing OS processes is dangerous you shouldn't be doing it!
Killing processes associated with regular sessions is fine. If you kill a process associated a background process you are going to be in trouble.
Don't blame me because you killed the wrong session.
Vijay Dumpa said...Missing comma after the serial# and @inst_id
ALTER SYSTEM KILL SESSION 'SID,serial#,@inst_id' IMMEDIATE;
SELECT 'ALTER SYSTEM KILL SESSION ''' || SID || ',' || serial#||',@'||inst_id
|| ''' IMMEDIATE;'
WHERE status in ('SNIPED');
Thanks for the heads-up. The typo is corrected now.
I also notices the document links at the bottom of the page were no longer valid. I've updated those also.
Peter said...Nice article, straight to the point.
One thing I do not understand:
"Oracle 11g introduced the ALTER SYSTEM DISCONNECT SESSION syntax as a new method for killing Oracle sessions"
Disconnect session is documented in 8i, and maybe even before. What's new about?
Correct. I've amended the article.
Priscilla said...Thanks Tim, for sharing a very useful tip! I tried it today to kill a couple of sessions that kept reconnecting from an application server that nobody knew how to shut down.
joel garry said..."while the IMMEDIATE clause disconnects the session and ongoing transactions are recovered immediately."
Yes. Bad choice of words. I've altered it now. :) Thanks for the heads-up.