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?
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
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';
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@%'
I hope you like the content on alter system kill session. Please do let me know how you use this command in your environment
alter system switch logfile v/s archive log current
How to solve ORA-28000 the account is locked
How to alter table add column oracle
How to turn on the SQL trace, 10046 event in Oracle database and trcsess, tkprof utility
How to List All Tables in Oracle