-- set up variables tx_id and trace_file
column txid new_value tx_id
column tracefile new_value trace_file
-- ask user for the Transaction id from v$lock id1 or enqueue TX p1
select &txid txid from dual;

-- create sql script to dump the rollback block that
-- the transaction id is in
-- spool it to txid.sql and run txid.sql
set heading off
set feedback off
set verify off
spool txid.sql
select 
    	'alter system dump datafile '||t.ubafil||' block '|| t.ubablk||';'
  from
        v$transaction t
  where 
        t.xidusn =   (TRUNC(&tx_id/power(2,16)))
    and t.xidslot =  (BITAND(&tx_id,TO_NUMBER('ffff','xxxx'))+0)
/
spool off
@txid


-- 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' tracefile
from
      v$process p,
      v$session s
where s.paddr = p.addr
  and s.sid = ( select distinct sid from v$mystat)
/


-- create a shell script to grep for the
-- UNDO record in the UNDO dump file
-- put into objid.sh and run objid.sh
-- objid.sh when runs greps for the UNDO record
-- find the objn and spools this to a sql query
-- to translate the objn into the object name
spool objid.sh
set linesize 200
select
        'grep "Rec #0x'||trim(lower(to_char(t.ubarec,'XXXXXXXXXX'))) || '" ' || '&trace_file' ||
        ' | sed -e "s/.*objn:/select object_name from dba_objects where object_id=/" | sed -e "s/(.*
/;/" > objn.sql '
  from
        v$transaction t
  where
        t.xidusn =   (TRUNC(&tx_id/power(2,16)))
    and t.xidslot =  (BITAND(&tx_id,TO_NUMBER('ffff','xxxx'))+0)
/
set linesize 80
spool off
!sh objid.sh
@objn


-- gather additional info about UNDO from the UNDO dump
--
spool undoinfo.sh
set linesize 200
select
        'awk ''/Rec #0x'||trim(lower(to_char(t.ubarec,'XXXXXXXXXX'))) ||'/,/^.$/{print}''' || ' &tra
ce_file'
from
        v$transaction t
  where
        t.xidusn =   (TRUNC(&tx_id/power(2,16)))
    and t.xidslot =  (BITAND(&tx_id,TO_NUMBER('ffff','xxxx'))+0)
/
set linesize 80
spool off
!sh undoinfo.sh