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 '%[email protected]%'
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';
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
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 : 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