PL/SQL check blocking sessions

—Recipie #1 – find blocking sessions with v$session
SELECT
   s.blocking_session,
   s.sid,
   s.serial#,
   s.seconds_in_wait
FROM
   v$session s
WHERE
   blocking_session IS NOT NULL;
—Recipie #2 – find blocking sessions using v$lock
SELECT
   l1.sid || ‘ is blocking ‘ || l2.sid blocking_sessions
FROM
   v$lock l1, v$lock l2
WHERE
   l1.block = 1 AND
   l2.request > 0 AND
   l1.id1 = l2.id1 AND
   l1.id2 = l2.id2;
/*Recipie #3 – blocking sessions with all available information
The next query prints a few more information, it let’s you quickly see who’s blocking who. Run this query and you can immediately call the colleague who’s locking your table:
*/

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 ;
–Recipie #4 – identifying blocked objects

SELECT sid, id1 FROM v$lock WHERE TYPE=’TM’;

 
use this scripts i hope its it will help u to get blocking locks

set echo off
set line 10000
column blocker format a11;
column blockee format a10;
column sid format 99999;
column blocker_module format a15 trunc;
column blockee_module format a15 trunc;

alter session set optimizer_mode=rule;

select a.inst_id,
(select username from gv$session s where s.inst_id=a.inst_id and s.sid=a.sid) blocker,
a.sid,
(select module from gv$session s where s.inst_id=a.inst_id and s.sid=a.sid) blocker_module ,
‘ is blocking ‘ “IS BLOCKING”,
b.inst_id,
(select username from gv$session s where s.inst_id=b.inst_id and s.sid=b.sid) blockee,
b.sid ,
(select module from gv$session s where s.inst_id=b.inst_id and s.sid=b.sid) blockee_module
from gv$lock a, gv$lock b
where
–Modified to take care of issue where sid blocked on difft instances are not showing
— Original Script
/* a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.inst_id = b.inst_id
and a.id2 = b.id2
*/
— Modified script
a.block <>0 and
b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2
and a.sid <> b.sid
order by 1, 2
/
alter session set optimizer_mode=choose;

 

—————–==============================
—————–==============================

 

 

 

 

 

 
select * from ec_branch_hurdles
where book_size is not null

select count(*)
–delete
from ec_branch_hurdles where report_id>20130101;
commit;

select * from ec_report_date_details order by batch_id desc;

–delete from ec_employee_branches where report_id =20130426;
–delete from ec_et_employees where report_id =20130426 ;
select * from ec_acct_credits where report_id =20130426 ;
select * from ec_employee_hurdles  where report_id =20130426 ;
select * from ec_branch_costs  where report_id =20130426 ;
select * from ec_branch_hurdles  where report_id =20130426 ;

set linesize 140
set pages 100
col username       format a20
col “SID,SESSION#” format a20
col sess_id        format a10
col object format a30
col mode_held      format a10
select     oracle_username || ‘ (‘ || s.osuser || ‘)’ username
,  s.sid || ‘,’ || s.serial# “SID,SESSION#”
,  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;

Leave a Reply

Your email address will not be published. Required fields are marked *