Recent site activity

Oracle: ash_tx_lock.sql

/*

ST    EVENT                    SID  LM         P2     P3 OBJN               OTYPE       FN BLOCKN SQL_ID         BSID
----- ---------------------- ----- --- ---------- ------ ------------------ ---------- --- ------ --
08:34 enq: TX - index cont     135   4     524296   3022 -1                              0      0 635xhydd6fzgg   136
08:34 enq: TX - index cont     128   4    1507348   2690 -1                              0      0 1hsb81ypyrfs5   134
08:34 enq: TX - index cont     127   4     786449   2931 -1                              0      0 610b5yphj50vt   129
08:34 enq: TX - index cont     124   4     786449   2931 -1                              0      0 bb1v723s9p5hz   129
08:34 enq: TX - index cont     119   4     983083   2922 -1                              0      0 7trxarnckx3g3   132
08:34 enq: TX - index cont     127   4    1572890   2906 -1                              0      0 610b5yphj50vt   124
08:34 enq: TX - index cont     129   4     196646   3242 -1                              0      0 5x0fksgfwkn6s   133
08:34 enq: TX - index cont     139   4     786461   2931 BBW_INDEX_VAL_I    INDEX        1  64826 6avm49ys4
08:34 enq: TX - index cont     136   4     786461   2931 -1                              0      0 5wqps1quuxqr4   135
08:34 enq: TX - index cont     119   4     983083   2922 -1                              0      0 7trxarnckx3g3   132
08:34 enq: TX - index cont     127   4    1572890   2906 -1                              0      0 610b5yphj50vt   124
*/


col event for a22
col block_type for a18
col objn for a18
col otype for a10
col fn for 99
col sid for 9999
col bsid for 9999
col lm for 99
col p3 for 99999
col blockn for 99999
select
       to_char(sample_time,'HH:MI') st,
       substr(event,0,20) event,
       ash.session_id sid,
       mod(ash.p1,16)  lm,
       ash.p2,
       ash.p3, 
       nvl(o.object_name,ash.current_obj#) objn,
       substr(o.object_type,0,10) otype,
       CURRENT_FILE# fn,
       CURRENT_BLOCK# blockn, 
       ash.SQL_ID,
       BLOCKING_SESSION bsid
from v$active_session_history ash,
      all_objects o
where event like 'enq: TX%'
   and o.object_id (+)= ash.CURRENT_OBJ#
   and sample_time > sysdate - 40/(60*24)
Order by sample_time
/
Comments