R12.2 Apps DBA. Powered by Blogger.

How to Kill Inactive session in Oracle Database

No comments :
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.
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;
Use the output of the above query to kill the particular session using the below sql query.
ALTER SYSTEM KILL SESSION '231,23454'
OR
kill -9 PID on OS level

No comments :

Post a Comment

Note: only a member of this blog may post a comment.