Alter system kill session command
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 alter system kill 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 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 Give access for alter system kill session to any user
We dont have any privilege like alter system kill session which can be given to the user. But this can be achieved by creating a procedure and then giving execute on that procedure to the user
Support we want to give kill session access to the user SCOTT to kill its own session
CREATE OR REPLACE PROCEDURE sys.kill_session(p_sid NUMBER, p_serial NUMBER) AS v_user VARCHAR2(30); BEGIN SELECT MAX(username) INTO v_user FROM v$session WHERE sid = p_sid AND serial# = p_serial; IF v_user IN ('SCOTT') THEN --the list can be extended EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || p_sid || ',' || p_serial || ''''; ELSIF v_user IS NULL THEN RAISE_APPLICATION_ERROR(-20001,'Session has Expired or Invalid sid/serial Arguments Passed'); ELSE RAISE_APPLICATION_ERROR(-20002,'Unauthorized Attempt to Kill a Non User Session has been Blocked.'); END IF; END kill_session; / or create or replace procedure kill_session( p_sid in number, p_serial# in number) is ignore pls_integer; BEGIN select count(*) into ignore from V$session where username = USER and sid = p_sid and serial# = p_serial# ; if ( ignore = 1 ) then execute immediate ' alter system kill session ''' || to_char(p_sid,'999999')||','|| to_char(p_serial#,'999999')||''''; else raise_application_error( -20001, 'You do not own session ''' || p_sid || ',' || p_serial# || '''' ); end if; END; / grant execute on kill_session to SCOTT;
I hope you like the content on alter system kill session. Please do let me know how you use this command in your environment