An Oracle DBA may occasionally have to kill specific user sessions on a database instance. Some common scenarios where you have to do this is when you have to recreate a schema or to kill inactive user session and free up the resources. The safest method to perform this task without losing or corrupting any data is to correctly identify the session to be killed and then using the ALTER SYSTEM KILL SESSION SQL statement.
When a session is killed, any active transaction is rolled back and the resources held by that session are released.
Identifying the session
The dynamic performance view V$_SESSION contains information about all current sessions. Database administrators can query this view as SYS user using the public synonym V$SESSION to identify the sessions that you want to terminate. The following query for example list all user sessions:
SELECT SID, SERIAL#, USERNAME, STATUS, SCHEMANAME, OSUSER, MACHINE
FROM V$SESSION
WHERE TYPE='USER';
SID SERIAL# USERNAME STATUS SCHEMANAME OSUSER MACHINE
------ ---------- ---------- -------- ------------ ---------- -----------
131 18 TECH INACTIVE TECH Scot SCOTSPC
194 11 SYS ACTIVE SYS Paul DBSERVER
Session are identified using SID and SERIAL#.
Terminating the session
Once the session is identified you can terminate it using the ALTER SYSTEM KILL SESSION statement together with that sessions SID and SERIAL#. The syntax is:
ALTER SYSTEM KILL SESSION 'SID,SERIAL#';
The following SQL statement kills the user session with the system identifier 131 and serial number 18
ALTER SYSTEM KILL SESSION '131,18';
System altered.
The session status V$SESSION is now changed to KILLED
SELECT SID, SERIAL#, USERNAME, STATUS, SCHEMANAME, OSUSER, MACHINE
FROM V$SESSION
WHERE TYPE='USER';
SID SERIAL# USERNAME STATUS SCHEMANAME OSUSER MACHINE
------ ---------- ---------- -------- ------------ ---------- -----------
131 18 TECH KILLED TECH Scot SCOTSPC
194 11 SYS ACTIVE SYS Paul DBSERVER
If you kill a session that is active the transaction that are running are rolled back and session locks are released. The user will then receive the message
ORA-00028: your session has been killed
In case of an inactive session the user receives this message in the next attempt to use that terminated session.
The information about the terminated session is removed from V$SESSION after the ORA-00028 message is issued.
If the user session that you are trying to kill has some ongoing transactions then Oracle DB will wait for up to one minute for the transaction to finish. If the wait period exceeds one minute, the session is marked as terminated and the control will be returned to you.
You can use the IMMEDIATE clause to instruct kill the session immediately rather than waiting for the transaction to finish. The syntax for this is:
ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;
