How to Kill Oracle Sessions?

Published by Jignesh Jethwa
Oracle 11g Logo

Requesting you to be more careful while killing session, in case of you will kill the wrong session, it will be very destructive.

By mistake, In case if you kill background process, it will cause an instance crash.

Syntax:
ALTER SYSTEM KILL SESSION ‘sid,serial#’;

Example:
Lets login to SCOTT user to create session on Oracle database, as follow:

[oracle@dbserver ~]$ sqlplus “scott/tiger”
SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 20 17:29:46 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Release 11.2.0.3.0 – 64bit Production

SQL> show user
USER is “SCOTT”

Now, Lets login to SYS user to kill SCOTT schema session and issue the following query to identify the session to be killed.

Use GV$SESSION and GV$PROCESS views as follows:

[oracle@dbserver ~]$ sqlplus “/ as sysdba”
SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 20 17:51:44 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Release 11.2.0.3.0 – 64bit Production

SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45

SELECT s.inst_id, s.sid, s.serial#, p.spid, s.username, s.program
FROM gv$session s 
JOIN
gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != ‘BACKGROUND’;

INST_ID  SID        SERIAL#    SPID       USERNAME   PROGRAM
——– ———- ———- ———- ———- ————————————
1        36         39239      63244      SYS        sqlplus@dbserver (TNS V1-V3)
1        801        17561      7504       SCOTT      sqlplus@dbserver (TNS V1-V3)
1        398        25869      60672      PUR4       frmweb@galaxy.erp.com (TNS V1-V3)
1        405        47052      60672      PUR4       frmweb@galaxy.erp.com (TNS V1-V3)

The SID and SERIAL# values will be the input for the query to to kill the session.

Issue following query to kill the session:

ALTER SYSTEM KILL SESSION ‘801,17561’;

System altered.
After SCOTT schema session killed, it will be forcefully disconnected by error message:

SQL> select * from SCOTT.History;
select * from SCOTT.History
*
ERROR at line 1:
ORA-00028: your session has been killed

 

In case of Real application Cluster ( RAC ) environment, you can specify the INST_ID ( optional ), This will allows you to kill a session on different RAC node.

ALTER SYSTEM KILL SESSION ‘sid,serial#,@inst_id’;

 

While killing session, if you specify IMMEDIATE clause than session will be killed immediately whether current transaction completed or not.

In case of KILL SESSION, It’s illegible for being killed by itself, but in case of any current transaction is running, then KILL SESSION will wait to complete that transaction. As soon as the transaction completed that session has been killed itself.

Syntax:
ALTER SYSTEM KILL SESSION ‘sid,serial#’ IMMEDIATE;

It won’t affect the performance by the command, but it returns control back to the current session immediately, instead waiting for confirmation of the kill.

***********************************************************************

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

Leave a Reply