How to Kill Inactive session in Oracle Database

The following is the process or steps to kill the inactive session in Oracle Apps.



Step 1 

Find the current locked session using the following query.
select sid,
       decode(block, 0, 'NO', 'YES') BLOCKER,
       decode(request,0,'NO','YES') WAITER
 from v$lock
 where (request > 0 or block > 0)
 AND decode(block  ,0,'NO','YES') ='YES' 
 order by block desc;

Run the above query in some time interval to understand whether the locked session ID is temporary or permanent. That means if one particular session ID is coming constantly that means the session ID is locked.


Step 2 

Run the below query to get the serial# along with the session ID. Pass value of the "" whatever you have got from above query.

 SELECT l.session_id||','||v.serial# sid_serial,
l.ORACLE_USERNAME ora_user,
o.object_name,
o.object_type,
DECODE(l.locked_mode,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive',
TO_CHAR(l.locked_mode)
) lock_mode,
o.status,
to_char(o.last_ddl_time,'dd.mm.yy') last_ddl
FROM dba_objects o, gv$locked_object l, v$session v
WHERE o.object_id = l.object_id
and l.SESSION_ID=v.sid
and l.SESSION_ID =
order by 2,3;


Step 3

Use the output of the above query to kill the particular session using the below sql query.
ALTER SYSTEM KILL SESSION '231,23454'