Killing Oracle Sessions

Oracle Tips by Burleson Consulting
Don Burleson

One of the most common mistakes that I see are DBA's killing the wrong session. When killing a session, carefully review the session, process and SQL associated with the offensive task. Before killing the session, the DBA may decide to view the SQL being executed by the session. This can be obtained by using the TID above (300) in the following SQL statement:

select
   b.username,
   a.sql_text
from
   v$sqltext_with_newlines a,
   v$session b,
   v$process c
where
   c.spid = to_number('300', 'xxx')
and
   c.addr = b.paddr
and
   b.sql_address = a.address;

Killing session with Oracle can be a challenge and different procedures exist within Windows and UNIX. Here are the steps that I use to kill an Oracle session:

1 - Gather session information from Oracle

2 - Kill the session at the OS-level. See UNIX/Linux kill command.

3 - Kill the session within Oracle using the "alter system kill session" command:

a) UNIX - I always locate the Server PID (SPID) from v$process and issue the UNIX kill -9 command.

b) The Windows command to kill this session would be as follows.

C:\oracle9i\bin>orakill ORCL92 768

In this example, the windows thread corresponding to the Oracle session can be killed in the operating system without ever logging into the database.

For these and hundreds of other useful Oracle scripts, I highly recommend our collection of Oracle scripts, and you can download them here:

www.dba-oracle.com/oracle_scripts.htm

Also see: Using dbms_shared_pool.purge to remove a single task from the library cache

Note: It's not uncommon for a OS process to continue to exist in v$session after you have killed an Oracle session with the "alter system kill session" command, because the OS process ID (PID) continues to eist. See MOSC note 1023442.6 for complete details.

Here are the Oracle scripts that I use to gather information before killing a session:

session.sql script

rem session.sql - displays all connected sessions
set echo off;
set termout on;
set linesize 80;
set pagesize 60;
set newpage 0;

select
rpad(c.name||':',11)||rpad(' current logons='||
(to_number(b.sessions_current)),20)||'cumulative logons='||
rpad(substr(a.value,1,10),10)||'highwater mark='||
b.sessions_highwater Information
from
   v$sysstat a,
   v$license b,
   v$database c
where
a.name = 'logons cumulative'
;

ttitle "dbname Database|UNIX/Oracle Sessions";

set heading off;
select 'Sessions on database '||substr(name,1,8) from v$database;
set heading on;

select
   substr(a.spid,1,9) pid,
   substr(b.sid,1,5) sid,
   substr(b.serial#,1,5) ser#,
   substr(b.machine,1,6) box,
   substr(b.username,1,10) username,
   -- b.server,
   substr(b.osuser,1,8) os_user,
   substr(b.program,1,30) program
from
   v$session b,
   v$process a
where
b.paddr = a.addr
and type='USER'
order by spid;
ttitle off;
set heading off;
select 'To kill, enter SQLPLUS> ALTER SYSTEM KILL SESSION',
''''||'SID, SER#'||''''||';' from dual;
spool off;