All about alter system kill session in Oracle database


Alter system kill session command


alter system kill session is a very important command for Oracle DBA. We can kill any session in the oracle database with the help of it

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 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


Related Articles

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


 

Leave a Reply