How to kill Inactive sessions in Oracle Database?

Sometimes there are so many inactive sessions available in the database. Due to these inactive sessions, and database responds slow performance so we need to kill them using the below steps.

Step 1. Find total session details.

SQL> select status,count(*) from v$session group by status;

Step 2. Find sid & serial# for inactive sessions.

SQL> SELECT sid, serial#, status, username FROM v$session where status='INACTIVE';

Step 3. Kill session using below command.
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE; 
If you have multiple sessions for killing, you must prepare the script.
Script for all sessions:
SQL> SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' FROM v$session;
The script only for Inactive sessions:
SQL> SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' FROM v$session 
     where status='INACTIVE';                                                  
The above queries will write the queries for you to kill Inactive sessions 
to makes your task very easy.


SET LINESIZE 250
COL USERNAME FOR A12
COL OBJECT_NAME FOR A10
COL SID FOR A20
COL LOCK_MODE FOR A10
COL CTIME FOR A30
COL MACHINE FOR A25
SELECT S.USERNAME,
SUBSTR(O.NAME, 1, 15) OBJECT_NAME,
DECODE(LV, 1, 'Holder: ' || S.SID, 'Waiter: ' || S.SID) SID,
DECODE(L.LMODE, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', 
TO_CHAR(L.LMODE)) LOCK_MODE,
  TRUNC(L.CTIME / 3600) || ':' || TRUNC(MOD(L.CTIME, 3600) / 60) || ':' || MOD(L.CTIME, 60) CTIME,
  S.STATUS,
  S.MACHINE,
  S.SQL_ID,
  Q.SQL_TEXT
FROM (SELECT /*+ NO_MERGE */(3-LEVEL) LV,
  INST_ID,
  SID,
  TYPE,
  LMODE,
  CTIME
  FROM (SELECT /*+ NO_MERGE */A.INST_ID,
  A.SID,
  A.TYPE,
  A.LMODE,
  A.REQUEST,
  CASE
  WHEN REQUEST = 0 THEN ID1
  END ID1,
  CASE
  WHEN REQUEST > 0 THEN ID1
  END ID3,
  A.CTIME
  FROM GV$LOCK A
  WHERE A.TYPE <> 'MR') START WITH REQUEST > 0 CONNECT BY PRIOR ID3 = ID1) L,
  GV$SESSION S,
  GV$PROCESS P,
  SYS.OBJ$ O,
  GV$SQL Q
WHERE L.SID = S.SID
AND L.INST_ID = S.INST_ID
AND S.INST_ID = P.INST_ID(+)
AND S.PADDR = P.ADDR(+)
AND S.ROW_WAIT_OBJ# = O.OBJ#(+)
AND L.CTIME >= 1
AND S.SQL_ID = Q.SQL_ID(+)
GROUP BY DECODE(LV, 1, 'Holder: ' || S.SID, 'Waiter: ' || S.SID),
 S.INST_ID, S.USERNAME, O.NAME, L.TYPE, L.LMODE, L.CTIME, S.STATUS,
 S.MACHINE,S.SQL_ID,Q.SQL_TEXT;

Identify SERIAL# of Holder: 
To identify the Serial# against the SID of Holder:

select action, sid, serial#, seconds_in_wait , status , client_identifier, 
logon_time , module  from v$session where sid=<SID of Holder>;

Example:
select action, sid, serial#, seconds_in_wait , status , client_identifier, 
logon_time , module  from v$session where sid=321;

Kill Database Lock:

alter system kill session '<SID>,<SERIAL#>';

alter system kill session '321,6868';