There are a number of reasons to kill non-essential Oracle user
processes. In Oracle the alter system kill session command allows you to
kill an Oracle session. Also see:
Using
dbms_shared_pool.purge to remove a single task from the library cache
The alter system kill session command requires two unique arguments
that uniquely identify the Oracle session, the
session identifier and serial number.
First you have to identify the session to be killed
with alter system kill session.
The SID and SERIAL# values of the Oracle session to be killed can
then be substituted and the alter system kill session command issued.
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';
Sometimes Oracle.exe is not able to kill the session immediately with
the alter system kill session command alone. Upon issuing the
alter system kill session command, the session will be ‘marked for kill'. It
will then be killed as soon as possible.
In the case of a session being 'marked for kill' after issuing the
alter system kill session command and not killed immediately, the
alter system kill session command can be forced by adding the
immediate
keyword:
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#'
IMMEDIATE;
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
