How to find and kill a session in Oracle: During this article we are going to discuss the way to find and kill Oracle sessions. Many times we are facing issue in Database Level, then we would to check and find out sessions details, how much there are active/inactive/killed sessions and might killed some sessions.
Find and kill a Oracle Session:
You can find out and kill oracle active/inactive/killed session by two ways:
- At Database Level.
- At OS Level.
Database Level:
We will kill a session at Database Level using sid and serial#. Using below commands we will be told and kill a INACTIVE session:
SQL> set lines 400 pages 400
SQL> col USERNAME for a30
SQL> select USERNAME, SID, SERIAL#, STATUS, SQL_ID from v$session where status='INACTIVE';
USERNAME SID SERIAL# STATUS SQL_ID
------------------------------ ---------- ---------- -------- -------------
AKASH 12 19428 INACTIVE 3p5u5z1h30d9c
PRASANTA 13 31126 INACTIVE 195298t344z0t
TEST 15 53334 INACTIVE
ORAKLDBA 17 50020 INACTIVE 427jx8nd02czs
SQL>
SQL>
SQL> select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where status='INACTIVE';
'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||'''IMMEDIATE;'
------------------------------------------------------------------------------------------------------------------------
alter system kill session '12,19428' immediate;
alter system kill session '13,31126' immediate;
alter system kill session '15,53334' immediate;
alter system kill session '17,50020' immediate;
SQL>
SQL> alter system kill session '12,19428' immediate;
System altered.
SQL>
Using below commands we are able to be told and kill a KILLED session:
SQL> set lines 400 pages 400
SQL> col USERNAME for a30
SQL> select USERNAME, SID, SERIAL#, STATUS, SQL_ID from v$session where status='KILLED';
USERNAME SID SERIAL# STATUS SQL_ID
------------------------------ ---------- ---------- -------- -------------
AKASH 564 279 KILLED 3p5u5z1h30d9c
PRASANTA 683 6167 KILLED 195298t344z0t
ORAKLDBA 1044 7191 KILLED 427jx8nd02czs
SQL>
SQL> select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where status='KILLED';
'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||'''IMMEDIATE;'
------------------------------------------------------------------------------------------------------------------------
alter system kill session '564,279' immediate;
alter system kill session '683,6167' immediate;
alter system kill session '1044,7191' immediate;
SQL>
SQL> alter system kill session '564,279' immediate;
System altered.
SQL>
OS Level :
We will killed a session at OS Level using spid. Using below commands we are able to discover spid and kill a INACTIVE session:
SQL> Set lines 400 pages 400
SQL> col sid format 99999
SQL> col username format a30
SQL> col osuser format a30
SQL> select a.sid, a.serial#,a.username, a.osuser, b.spid from v$session a, v$process b
2 where a.paddr= b.addr
3 and a.sid in (select a.sid from v$session a, v$sqlarea b, v$process c
4 where a.sql_address=b.address and a.sql_hash_value=b.hash_value
5 and a.paddr=c.addr
6 and a.username is not null
7 and a.status='INACTIVE');
SID SERIAL# USERNAME OSUSER SPID
------ ---------- ------------------------------ ------------------------------ ------------
1944 7591 AKASH MACHINE-3 19428
653 3177 PRASANTA PRASANTA-01 31126
294 279 ORAKLDBA ORAKLDBA 50020
SQL>
[oracle@primary ~]$ kill -9 19428
[oracle@primary ~]$
[oracle@primary ~]$ kill -9 31126
[oracle@primary ~]$
[oracle@primary ~]$ kill -9 50020Using below commands we will understand spid and kill a KILLED session:
SQL> Set lines 400 pages 400
SQL> col sid format 99999
SQL> col username format a30
SQL> col osuser format a30
SQL> select a.sid, a.serial#,a.username, a.osuser, b.spid from v$session a, v$process b
2 where a.paddr= b.addr
3 and a.sid in (select a.sid from v$session a, v$sqlarea b, v$process c
4 where a.sql_address=b.address and a.sql_hash_value=b.hash_value
5 and a.paddr=c.addr
6 and a.username is not null
7 and a.status='KILLED');
SID SERIAL# USERNAME OSUSER SPID
------ ---------- ------------------------------ ------------------------------ ------------
1944 7591 AKASH MACHINE-3 19428
653 3177 PRASANTA PRASANTA-01 31126
294 279 ORAKLDBA ORAKLDBA 50020
SQL>
[oracle@primary ~]$ kill -9 19428
[oracle@primary ~]$
[oracle@primary ~]$ kill -9 31126
[oracle@primary ~]$
[oracle@primary ~]$ kill -9 50020Note:
When, Database performance is to much slow then user requested to take care for the issue. And requested to kill all inactive sessions which are inactive quite half-hour. During this case you’ll be able to see all inactive sessions using below query :
SQL> Set lines 400 pages 400
SQL> col sid format 99999
SQL> col username format a30
SQL> col osuser format a30
SQL> select a.sid, a.serial#,a.username, a.osuser, b.spid from v$session a, v$process b
2 where a.paddr= b.addr
3 and a.sid in (select a.sid from v$session a, v$sqlarea b, v$process c
4 where a.sql_address=b.address and a.sql_hash_value=b.hash_value
5 and a.paddr=c.addr
6 and a.username is not null
7 and a.last_call_et>(30*60)
8 and a.status='INACTIVE');
SID SERIAL# USERNAME OSUSER SPID
------ ---------- ------------------------------ ------------------------------ ------------
1944 7591 AKASH MACHINE-3 19428
294 279 ORAKLDBA ORAKLDBA 50020
SQL>
Using sid and serial# you can killed sessions at Database Level. Example:
SQL> alter system kill session '1944,294' immediate;
System altered.
SQL>
Using spid you can killed sessions at OS Level. Example:
[oracle@primary ~]$ kill -9 19428
[oracle@primary ~]$
[oracle@primary ~]$ kill -9 50020I hope my written content will helped you. Your suggestions/feedback are most welcome from orakldba.com.
Keep learning… Have a great day!!!
Please comment here for your any query related to above content. You can write to mail me through : orakldba@gmail.com.