Killing a User’s Session

SQL> SELECT sid, serial# FROM v$session
2* WHERE username='user_name';
SID SERIAL#
----------------
10 32

SQL> ALTER SYSTEM KILL SESSION '10, 32’;
System altered.

If the session you want to kill is involved in a long operation, such as a lengthy rollback, Oracle will inform you that the session is marked for kill and it will be killed after the operation is completed.

When Oracle kills a session, it rolls back all ongoing transactions and releases all session locks.

If the UNIX process of the user is killed, the Oracle session will most likely be killed also, but that isn’t the most graceful way to end a session.

OS Level - If you want to End the section - USE KILL

If you think you must kill a user’s UNIX session, and the Oracle KILL SESSION command isn’t working, or it’s taking a long time, you can terminate the session rather abruptly by using the UNIX kill command as follows.

$ kill -9 345678

Use the SQL Syntax - to find the - USER

SQL> SELECT process,sid,serial# FROM v$session
WHERE username='&user';

Enter value for user: SOMEUSER
old 2: username='&user'
new 2: username='SOMEUSER'
PROCESS SID SERIAL#
-----------------------------------
2920:2836 10 34

How about working with WINDOWS?

ORAKILL - Utility

Oracle user’s session on Windows, can use the ORAKILL utility, which will kill a specific thread under the Oracle .exe process.

How do you find out what that user’s thread is?

SQL> SELECT sid, spid as thread, osuser, s.program
2 FROM v$process p, v$session s
3* WHERE p.addr = s.paddr;

SID THREAD OSUSER PROGRAM
-------------------------------------------------------------
1 1192 SYSTEM ORACLE.EXE
2 1420 SYSTEM ORACLE.EXE
3 1524 SYSTEM ORACLE.EXE
4 1552 SYSTEM ORACLE.EXE
5 1528 SYSTEM ORACLE.EXE
6 1540 SYSTEM ORACLE.EXE
7 1580 SYSTEM ORACLE.EXE
8 1680 SYSTEM ORACLE.EXE
9 2948 NETBSA\user sqlplusw.exe
10 4072 NETBSA\user sqlplusw.exe
10 rows selected.

Find the thread number where you want to kill...

C:> orakill 2948