Kill session in Oracle

---

Alter system kill session command

---

alter system kill session is a very important command for Oracle DBA. We can kill oracle session in the oracle database with the help of it. So this is useful for people looking for the answer of the question How do you kill a session in Oracle?

Syntax

alter system kill session 'sid, serial#';

Here sid, serial# can be obtained from v$session view.

select sid, serial# from v$session where username='SCOTT';

For Oracle RAC database
You can either login to same instance where session is running and then run the above alter system kill session command or you can use the below command also

ALTER SYSTEM KILL SESSION 'SID, SERIAL#, @INSTANCE_ID'

Where instance_id is the instance where that session is running.
This command comes handy when you want to kill multiple session from multiple instance

We generally used this command when we want to clear session which is inactive and holding locks, long running session

If the command is not able to kill the session as it has to undo lot of transaction, it will return as Marked for Killed. Once the undo is over, it will be killed itself. If the session is not doing undo but it is stuck somewhere, you can kill the server process in the background to clear the session.

As such we can kill the background session in case of Undo also, it will still take time to clear the undo but in fast manner. Generally it is not recommended to kill session from background which has lot of undo to performed

SQL> ALTER SYSTEM KILL SESSION ’42,2233';

ALTER SYSTEM KILL SESSION ’42,2233'
*
ERROR at line 1:
ORA-00031: session marked for kill

SQL> select username, status from v$session where SID=12;

USERNAME STATUS
——————– ——–
SCOTT KILLED

I have a another post which in talk in detail about transaction rollback. you can check for undo case

How to monitor transaction Rollback Progress

Now how to kill the server process associated with the session

–non RAC databases

SELECT s.sid, s.serial#, p.spid
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND username = 'SCOTT';

–RAC databases

SELECT s.inst_id, s.sid, s.serial#, p.spid
FROM gv$session s, gv$process p
WHERE s.paddr = p.addr
AND s.inst_id = p.inst_id
AND username = 'SCOTT';

Once you receive the SPID , you can login to the database server and kill the spid

ps -ef |grep <SPID>

Confirm this is Oracle database shadow process. and then kill it

kill -9 <SPID>

For Window, command will be

orakill SID SPID

We can also use kill session command like

ALTER SYSTEM KILL SESSION 'SID, SERIAL#, @INSTANCE_ID' IMMEDIATE;

The only difference it makes is that it return the control immediately. Otherwise in case for Marked for kill session ,it will wait for 5 min.

Terminating an Inactive Session

If the session is not making a SQL call to Oracle Database (is INACTIVE) when it is terminated, the ORA-00028 message is not returned immediately. The message is not returned until the user subsequently attempts to use the terminated session.

When an inactive session has been terminated, the STATUS of the session in the V$SESSION view is KILLED. The row for the terminated session is removed from V$SESSION after the user attempts to use the session again and receives the ORA-00028 message.

ORA-00028: your session has been killed

alter system disconnect session

There is another command which can be used to kill Oracle session

alter system disconnect session 'SID,SERIAL#' POST_TRANSACTION|IMMEDIATE;

Unlike kill session command, this command kills the dedicated server process (or virtual circuit when using Shared Sever), which is equivalent to killing the server process from the operating system. We can use POST_TRANSACTION in case, we need to wait for the transaction to commit or rollback. We will use immediate,if we want to immediately kill the session.

Some Useful Queries for kill oracle session

Query to generate kill session command for all session with the given schema Name

select 'alter system kill session ' ||''''|| sid||','|| serial#||''''||';' from v$session where SCHEMANAME='SCOTT'

Query to generate kill oracle session command for all session with the given module and status being inactive

col event format a30
col module format a15
col program format a30
set lines 100
select 'alter system kill session ' ||''''|| sid||','|| serial#||''''||';'
from v$session_wait sw, v$session s
where sw.sid = s.sid
and sw.sid in (select sid from v$session where module like '%&module%')
and s.status='INACTIVE';

Query to generate kill session command for all session which are connecting with sqlplus

select 'alter system kill session ' ||''''|| s.sid||','|| s.serial#||''''||';'
from v$session s where program like '%sqlplus@%'

how to check killed session in oracle

select sid, serial#, status, username , module, form
from v$session s where status like '%KILLED%'

how to remove killed session in oracle

We can remove the killed session by killing the server process associated. But Generally it is not recommended to killed session which has lot of undo to performed. You may want to use this if the killed session is still holding the locks as it got stuck somewhere

–non RAC databases

SELECT 'kill -9 '|| p.spid
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND s.status = 'KILLED';

–RAC databases

SELECT 'kill -9 '|| p.spid
FROM gv$session s, gv$process p
WHERE s.paddr = p.addr
AND s.inst_id = p.inst_id
AND s.status = 'KILLED';

how to check inactive session in oracle

select sid, serial#, status, username , module, form
from v$session s where status like '%INACTIVE%'

how to kill inactive session in oracle

select 'alter system kill session ' ||''''|| s.sid||','|| s.serial#||''''||';'
from v$session s where status like '%INACTIVE%'

If you getting insufficient privileges to kill session in oracle error, you may want to look at below post

oracle kill own session privilege

I hope you like the content on alter system kill session. Please do let me know how you use this command in your environment

Related Articles

alter system switch logfile : Check out this post for alter system switch logfile ,alter system archive log current command,how to use them and what all they performs in the background
ORA-28000 the account is locked : ORA-28000 the account is locked is very common error. Check out this post on how to solve it step by step easily without any issues
ORA-28002 :Check out this post on how to resolve ORA-28002 the password will expire. What can be done to altogether avoid it by creating new profile
alter table add column oracle :Useful insight into How to alter table add column oracle. Details about fast add column feature introduced in oracle 11g also given
Oracle tkprof : SQL trace or 10046 event is being used to trace the session activity in Oracle. The output generated by SQL trace is formatted using trcsess, tkprof utility
List All Tables in Oracle : we can get the List All Tables in Oracle by either querying all_tables or user_tables or dba_tables. we can select column and where clause as per the need
https://docs.oracle.com/cd/B28359_01/server.111/b28310/manproc008.htm#ADMIN11192

---