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;
Important Oracle 11g changes to alter session kill
session
Oracle author Mladen Gogala notes that an @ sign is now required to kill
a session when using the inst_id column:
SQL>
select inst_id,sid,serial# from gv$session where username='SCOTT';
INST_ID SID
SERIAL#
---------- ---------- ----------
1 130
620
SQL> alter system kill session '130,620,1';
alter
system kill session '130,620,1'
*
ERROR at line 1:
ORA-00026:
missing or invalid session ID
Now, it works:
SQL> alter system kill session '130,620,@1';
System altered.
For more notes on alter system kill session and how to kill a
session from the operating system side, see my notes on the pages below:
ALTER SYSTEM KILL SESSION notes
Granting ALTER SYSTEM KILL SESSION to users
Methods of Murder
Killing Oracle Sessions