Killing Sessions In Oracle

articles: 

Comments

gamyers's picture

If your session has done a lot of data changes and not yet committed, then it has probably generated a lot of undo.
When you kill a session, the process will try to apply the undo before responding back to the client with the "your session has been killed" message.
If you kill the process, then one of the background processes (I think PMON) will handle the undo.

Either way, the transaction will be kicking around until those data changes have been backed out.
You can't really speed it up. Just accept it.

The only time it is worth killing the OS process is when the client has been killed. In that case, the OS process can sit around waiting for the non-existent client to respond before it dies cleanly.

I use the following script to report current sessions. I like it because it provides th spid (OS PID) in the event that kill immediate does not work within Oracle. I have found that kill without the immediate clause often results in a long wait.

Best Regards --

set verify off
set echo off
set pagesize 1000
SET LINESIZE 120
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YY HH24:MI';

column  username    format a15     heading "User"
column  osuser      format a21     heading "OSUser"
column  logon_time  format a15     heading "Logged On"
column  program     format a30     heading "Program"
column  machine     format a26     heading "Machine"
select sid,s.serial#,spid,s.username,osuser,logon_time,s.program 
from v$process p, v$session s 
WHERE p.addr = s.paddr 
order by sid;

prompt To kill a session above, use the command:
prompt --   alter system kill session '[sid], [SERIAL#]' immediate

Use -

Alter system disconnect session 'SID,SERIAL#' IMMEDIATE ;

It will kill the dedicated process id on the OS .

Thanks,
Saurabh