Immediate Kill Session#: 1, Serial#: 3 Immediate Kill Session: sess: 6fa39d608 OS pid: 6419

I am pretty sure that many of us come across of situations when a killed session by 'alter system kill session' command did put the session in 'KILLED' status and never released the session for a long time on the database. It could be due to the fact that the session would be rolling back the ongoing transaction.
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:
Fri Mar 11 15:51:40 2016
Immediate Kill Session#: 726, Serial#: 9
Immediate Kill Session: sess: 6ea4a6dd8 OS pid: 6415
Immediate Kill Session#: 551, Serial#: 5
Immediate Kill Session: sess: 6ea45a280 OS pid: 6413
Immediate Kill Session#: 502, Serial#: 25
Immediate Kill Session: sess: 6e2446e78 OS pid: 8247
Immediate Kill Session#: 479, Serial#: 19
Immediate Kill Session: sess: 6e04473b0 OS pid: 8245
Immediate Kill Session#: 454, Serial#: 19
Immediate Kill Session: sess: 6e64528c0 OS pid: 8243
Immediate Kill Session#: 428, Serial#: 27
Immediate Kill Session: sess: 6e0431c08 OS pid: 8241
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.'
alter system kill session 'sid,serial#' IMMEDIATE;

More Information :

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: