Whenever we are in such situation, we generally try to find out the OS pid (on UNIX OS) associated with the killed session (which is a bit difficult task, as the killed session paddr in v$session changes while the addr corresponding value in v$process does not), and kill the associated OS process with 'kill -9' command on the OS level.
I have found the IMMEDIATE option with the 'alter system kill session' is more useful as it writes the following information in the alert.log file after killing the session and also try to finish the things at the earliest possible to close the session from the database:
Wed Feb 10 11:02:39 2010 Immediate Kill Session#: 515, Serial#: 36366 Immediate Kill Session: sess: c0000001be20d9f0 OS pid: 14686
As you see, it writes the time stamp when the session was killed, and also gives the associated OS pid of the killed session in the alert.log. As per Oracle documentation, 'Specify
IMMEDIATE
to instruct Oracle Database to roll back ongoing transactions, release all session locks, recover the entire session state, and return control to you immediately.'Syntax:
alter system kill session 'sid,serial#' IMMEDIATE;
Regards,
Jaffar
7 comments:
Very helpful Jaffar.
Thanks for posting !!
Nice post, Today I have known new point.
Thankyou bhai..
Excellent posting and thanks a lot for sharing great tip.
Did not know that.
Very useful tip.
Interesting post, thanks for sharing
Hi, Jaffar.
Pls, note that it is not case in 9i.
Regards
hi jafar,
thank you.its very useful to me.
Jp.
Post a Comment