One of the most common mistakes 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.
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
3. Kill the session within Oracle using the "alter system kill session" command:
- UNIX - I always locate the Server PID (SPID) from v$process and issue the UNIX kill -9 command.
- The Windows command to kill this session would be as follows:
C:\oracle9i\bin>orakill ORCL92 768
Here are the Oracle scripts example 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;
