Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Friday, 11 January 2013

Script: Check Blocker in Oracle 10g

prompt === Wait Chains ===
SELECT /*+ rule */
rpad(rpad('+', level ,'-'),4,' ')||lpad(wc.sid,4,' ')||','||lpad(wc.sess_serial#,5,' ') sidser
,wc.blocker_sid bsid,se.username orauser,se.osuser osuser,lpad(wc.osid,5)||'-'||lpad(substr(nvl(se.program,'null'),instr(se.program,'(')+1,4),4) sp,decode(wc.num_waiters,0,' ','x') w
,wc.wait_event_text event,least(se.p1,9999999999) p1,least(se.p2,999999) p2,least(se.p3,99) p3
,wc.row_wait_obj# wobj,nvl(se.module,'<'||substr(se.machine,1,instr(se.machine,'.')-1)||'>') module
,least(se.last_call_et,999) elaps
,decode (se.command, 0, ' 0 ', 1, 'CRTB', 2, 'ISRT', 3, 'SEL', 4, 'CRCL', 5, 'ALCL', 6, 'UPDT', 7, 'DEL'
, 8, 'DR', 9, 'CRIX',10, 'DRIX',11, 'ALIX',12, 'DRTB',15, 'ALTB',17, 'GRNT',18, 'REVK',19, 'CSYN',20, 'DSYN',21, 'CRVW',22, 'DRVW',26, 'LKTB',27, 'NOOP',28, 'RENM',29, 'CMNT',30, 'AUDT',31, 'NAUD',32,'CRLN'
,33, 'DRLN',34, 'CRDB',35, 'ALDB',36, 'CRRB',37, 'ALRB',38, 'DRRB',39, 'CRTS',40, 'ALTS',41, 'DRTS',42, 'ALSE',43, 'ALUS',44, 'COMT',45, 'RLBK',46, 'SVPT',47, 'PLSQ',62, 'ANTB',63, 'ANIX',64, 'ANCL',85, 'TRTB'
, to_char(se.command)) command
,nvl(se.sql_id,se.prev_sql_id)||':'||decode(se.sql_id,null,'p',se.sql_child_number) sqlid
FROM v$wait_chains wc,v$session se
where wc.sid = se.sid
and wc.sess_serial# = se.serial#
CONNECT BY PRIOR wc.sid = wc.blocker_sid
AND PRIOR wc.sess_serial# = wc.blocker_sess_serial#
AND PRIOR wc.INSTANCE = wc.blocker_instance
START WITH wc.blocker_is_valid = 'FALSE';
--Show blocking user sessions and the waiting user sessions
SELECT DISTINCT o.object_name, sh.username
  || '('   || sh.sid || ')' "Holder",  sw.username || '(' || sw.sid || ')' "Waiter",
  DECODE ( lh.lmode, 1, 'NULL', 2, 'row share', 3, 'row exclusive', 4, 'share',  5, 'share row exclusive',  6, 'exclusive'  ) "Lock Type"
 FROM all_objects o, v$session sw, v$lock lw, v$session sh, v$lock lh
 WHERE lh.id1 = o.object_id AND lh.id1 = lw.id1 AND sh.sid = lh.sid AND sw.sid = lw.sid
 AND sh.lockwait IS NULL  AND sw.lockwait IS NOT NULL AND lh.TYPE = 'TM' AND lw.TYPE = 'TM'


  1. Hi there

    85, 'TRTB' missing a single quote...