Find and resolve Blocking Session

Hi Everyone,

In the life of Oracle/Oracle Apps DBA, we usually fall in the pit of blocking session. First we need to find whether any blocking session is happening by the following query:-

select blocking_session, sid, serial#, wait_class, seconds_in_wait from v$session
where blocking_session is not NULL order by blocking_session;

If there is any blocking session, we must check the "second_in_wait" as well as the sid which is blocking. If the session wait is higher and the its inactive for long time, then we can get rid of that session by:-

alter system kill session 'sid,serial#' immediate;
for eg: alter system kill session '1140,188' immediate;

Other Useful Queries related with Locks & sessions:-

SELECT o.object_name,
v.session_id sid,
v.oracle_username,
decode(l.type,'TM', 'DML enqueue (TM)', 'TX', 'Transaction enqueue (TX)', 'UL', 'User supplied (UL)', l.type),
decode(l.lmode,0,'None',1,'Null',2,'Row Share',3,'Row Excl.',4,'Share',5,'S/Row Excl.',6,'Exclusive',to_char(l.lmode,'990')) holding,
decode(l.request,0,'None',1,'Null',2,'Row Share',3,'Row Excl.',4,'Share',5,'S/Row Excl.',6,'Exclusive',to_char(l.request,'990')) wanting,
l.block,
s.terminal,
s.machine
FROM sys.v_$locked_object v,
sys.v_$lock l,
sys.dba_objects o,
sys.v_$session s
WHERE l.sid = v.session_id
AND s.sid = v.session_id
AND v.object_id = o.object_id;


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, s.event
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
and l.sid = s.sid
and v.session_id = l.sid
and s.process = v.process
order by oracle_username
, session_id


select sid, sql_text
from v$session s, v$sql q
where sid in (select sid
from v$session where state in ('WAITING')
and wait_class != 'Idle' and event='enq: TX - row lock contention'
and (
q.sql_id = s.sql_id or
q.sql_id = s.prev_sql_id));

select SESSION_ID,NAME,P1,P2,P3,WAIT_TIME,CURRENT_OBJ#,CURRENT_FILE#,CURRENT_BLOCK#
from v$active_session_history ash, v$event_name enm
where ash.event#=enm.event#
-- and SESSION_ID=213 and SAMPLE_TIME>=(sysdate-&minute/(24*60));

CAN ALSO USE THE BELOW

select do.object_name,sid,s.serial#,s.osuser,
row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#, --s.session_id,
dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
from v$session s, dba_objects do
where s.ROW_WAIT_OBJ# = do.OBJECT_ID ;

Thanks
Punithavel