Wikipedia

Search results

Find the blocking session and lock in database


Find the blocking session in Database  :
============================

select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' )  is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2;



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


Checked for object lock: 
==================

set lines 1000
col object_name format a20
 select
     c.owner,
     c.object_name,
    c.object_type,
    b.sid,
   b.serial#,
     b.status,
   b.osuser,
     b.machine
  from
     v$locked_object a ,
     v$session b,
     dba_objects c
  where
     b.sid = a.session_id
  and
     a.object_id = c.object_id;

No comments:

Post a Comment