Home > Oracle > Oracle: How to find and kill a locked session ?

Oracle: How to find and kill a locked session ?

Run below as SYSTEM or SYS

select    oracle_username || ' (' || s.osuser || ')' username
,    s.sid || ',' || s.serial# sess_id
,    owner || '.' ||    object_name object
,    object_type
,    decode(    l.block
 ,    0, 'Not Blocking'
 ,    1, 'Blocking'
 ,    2, 'Global') status
,    decode(v.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(lmode)) mode_held
from    v$locked_object v
,    dba_objects d
,    v$lock l
,    v$session s
where     v.object_id = d.object_id
and     v.object_id = l.id1
and     v.session_id = s.sid
order by oracle_username
,    session_id

/

SELECT s.sid,
 s.serial#,
 s.osuser,
 s.program
FROM   v$session s;

ALTER SYSTEM KILL SESSION '15,2043' IMMEDIATE;
Advertisements
Categories: Oracle
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: