/*
    take as input the SID of a session blocked on a transactional lock
    dumps out relevant UNDO
*/


column f_txid new_value v_txid
column f_sid new_value v_sid
column f_block new_value v_block
column f_file new_value v_file
column f_uba new_value v_uba
column f_xuba new_value v_xuba
column f_rec new_value v_rec
column f_tracefile new_value v_tracefile

select &SID f_sid from dual;

set heading off
set feedback off
set verify off
spool blockdump.sql
select 
    	'alter system dump datafile '||
          ROW_WAIT_FILE#||
        ' block '|| 
          ROW_WAIT_BLOCK#||';'
  from
        v$session t
  where 
       sid=&v_sid
/
spool off
@blockdump

-- find the trace file name and save it to the variable trace_file
select
     (select value from v$parameter where name='user_dump_dest')||'/'||
     (select value from v$parameter where name='db_name')||
     '_ora_' || p.spid ||'.trc' f_tracefile
from
      v$process p,
      v$session s
where s.paddr = p.addr
  and s.sid = ( select distinct sid from v$mystat)
/



--            txid                uba
-- 0x02   0x0008.004.00083b1b  0x00801454.0608.31  ----   


set linesize 400
spool uba.sh
select
        'grep ' ||
     lpad(ltrim(to_char(
                   TRUNC(p2/power(2,16)
                        ),'XXXX')
            ,' ')
      ,4,'0') ||'.'||
     lpad(ltrim(
                to_char(
                  (
                    BITAND(524292,TO_NUMBER('ffff','xxxx'))+0
                  )
                ,'XXXX')
              ,' ')
           ,3,'0')  || ' &v_tracefile ' ||
                       ' | sed -e "s/^[^ ]*   //" '||
                       ' | sed -e "s/^[^ ]*  //"  '||
                       ' | sed -e "s/ .*$//"      '||
                       ' | sed -e "s/0x//"      '||
                       ' | sed -e "s/\.....\./ f_xuba , /"      '||
                       ' | sed -e "s/^/select /"  '||
                       ' | sed -e "s/$/ f_rec  from dual;/"  > uba.sql'
  from
     v$session_wait
  where
     sid=&v_sid
/
spool off
!sh uba.sh
@uba.sql


select to_number('&v_xuba','xxxxxxxx') f_uba from dual;
SELECT dbms_utility.data_block_address_block(&v_uba) f_block from dual;
SELECT dbms_utility.data_block_address_file(&v_uba) f_file from dual;



spool blockdump.sql

select 
    	'alter system dump datafile '||
          &v_file||
        ' block '|| 
          &v_block||';'
        from dual
/
spool off
@blockdump


-- gather additional info about UNDO from the UNDO dump
--
spool undoinfo.sh
set linesize 200
select
        'awk ''/Rec #0x'||&v_rec ||'/,/^.$/{print}''' || ' &v_tracefile'
from
      dual
/
set linesize 80
spool off
!sh undoinfo.sh