How to Kill a Session In Oracle Database
Introduction
In this blog How to Kill a Session In Oracle Database we can kill oracle session by using the sql command alter system kill session and also many ways to kill the session why we we kill the process bacause inactive and holding locks, process for long time it will occupies more memory and more resources the users are unable to login the database or hanging the system it will take take long time
Now let us start the process How to Kill a Session In Oracle Database
The syntax to kill a session in oracle database
ALTER SYSTEM KILL SESSION ‘SID,SERIAL#’ IMMEDIATE;
Here sid,serial# can be obtained from v$session view
select sid,serial# from v$session where username like 'CHAITANYA'
Step 1: first get the sid and serial# of the session;
Here the session is executing the query SELECT * FROM CHAITANYADBA;
Use the below query to get the sid and serial# of this sql query.
COL SQL_TEXT format a45
SQL> SELECT a.sid,a.serial#,substr(b.sql_text,1,200) sql_text from v$sql b,
v$session a where a.sql_id=b.sql_id and upper(b.sql_text)
like '%CHAITANYADBA%' and upper(b.sql_text) not like '%V$SQL%'; 2
SID SERIAL# SQL_TEXT
---------- ---------- ---------------------------------------------
33 26316 select * from chaitanyadba
Now kill session :
SQL> alter system kill session '33,26316' immediate;
System altered.
For Oracle RAC Database
You can login to the 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 the current session is running this command is useful when you want to kill multiple session from instance in oracle rac database,generally this command is used for 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 killed itself ,if the session is not doing undo but it is stuck some where ,you can kill the sever process in th ebackground to clear the session
SQL> ALTER SYSTEM KILL SESSION ’31,3123';
ALTER SYSTEM KILL SESSION ’31,3123'
*
ERROR at line 1:
ORA-00031: session marked for kill
SQL> select username, status from v$session where SID=12;
USERNAME STATUS
——————– ——–
CHAITANYA KILLED
How to kill the server process associated with session
NON RAC DATABASE
SELECT s.sid, s.serial#, p.spid
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND username = 'CHAITANYA';
RAC DATBASE
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 = 'CHAITANYA';
Once you executing this sql query we will get the SPID yo can login to database server and kill the SPID
ps -ef | gep <SPID>
Confirm this is oracle database shadow proces and kill it
kill -9 <spid>
DISCONNECT SESSION
There is another command which can be usedto kill oracle session
alter system disconnect session 'SID,SERIAL#' POST_TRANSACTION | IMMEDIATE;
USEFUL QUERIES FOR KILL ORACLE SESSION
Query to generate kill session command for all sessions with given schema name
select 'alter system kill session ' ||''''|| sid||','|| serial#||''''||';' from v$session where SCHEMANAME='CHAITANYA'
Here i am using CHAITANYA is the schema like 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 Genearte 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@%'
Query to check killed session in oracle
select sid, serial#, status, username , module, form
from v$session s where status like '%KILLED%'
Query to remove killed session in oracle database
Non RAC database
SELECT 'kill -9 '|| p.spid
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND s.status = 'KILLED';
RAC database
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';
Query to check inactive session in oracle database
select sid, serial#, status, username , module, form
from v$session s where status like '%INACTIVE%'
Query to kill inactive session in oracle
select 'alter system kill session ' ||''''|| s.sid||','|| s.serial#||''''||';'
from v$session s where status like '%INACTIVE%'
IDENTIFY THE SESSION USING THE GV$SESSION and GV$PROCESS VIEWS AS FOLLOWS
SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45
SELECT s.inst_id,
s.sid,
s.serial#,
--s.sql_id,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND';
INST_ID SID SERIAL# SPID USERNAME PROGRAM
---------- ---------- ---------- ---------- ---------- ---------------------------------------------
1 30 25 3859 PROD chaitanya@chaitu-12gr2.localdomain (TNS V1-V2)
1 13 387 3834 SYS abhiram@chaiu-12gr2.localdomain (TNS V1-V2)
1 30 487 4663 manasa@chaitu-12gr2.localdomain (Z003)
1 28 225 4665 pavan@chaitu-12gr2.localdomain (Z001)
Note: Info on How to Kill a Session In Oracle Database it maybe differ in your enviroment like production,testing ,development and naming conventions etc
THANKS FOR VIEWING MYBLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME
