Gracefully terminating sessions in Oracle

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;


Post a comment

Name

Your Comment

Email (We dont publish it)

Comments

Nothing yet..be the first to share wisdom.