Blog

In your daily DBA operational tasks, you may often find to kill a session in Oracle. Today we will see how to deal with that situation with proper and simple steps :

Steps to kill a session in Oracle :

Before killing a session it is required to check what is running by that session. So, we have to check if it is a about select statement or DML statement, also from which user it is running it is very much needed to ensure. Also, if you kill a background process, then it may lead to instance crash or outage, so be very careful while doing this and identify the correct session.

1. Find the sid,serial#,status,machine,username,program,sql_id from v$session

select sid,serial#,status,machine,username,program,sql_id from v$session where type!='BACKGROUND';

2. From the sql_id, you can get the sql_text by the below command :

select sql_text from v$session where sql_id='xxxxxxx';

3. There may few cases that arise here:
   i)If it is an internal query, then you can check with the team and kill it.
   ii)If it is select query running from the Application user, then you can check with them to kill it
   iii) But if there is any DML/DDL statement running from the Application user, just check with them before killing.
4. Query to kill any session in Oracle:

alter system kill session 'sid,serial#' immediate;

Steps to kill a session in RAC:

1. Find the sid,serial#,status,machine,username,program,sql_id from v$session

SELECT inst_id,
sid,
serial#,
username,
sql_id,
program
FROM gv$session
WHERE type != ‘BACKGROUND’;

2. From the sql_id, you can get the sql_text by the below command :

select sql_fulltext from gv$session where sql_id='xxxxxxx';

3. Query to kill any session in Oracle:

alter system kill session 'sid,serial#,inst_id' immediate;

Also, you may follow the below Oracle Doc for further reference : https://www.oracle.com/ocom/groups/public/@otn/documents/webcontent/283689.htm

Related Posts:

https://dbapostmortem.com/oracle-database-is-performing-slow/