-- SESSION and SQLTEXT
select sesion.sid,
       sql_text
  from v$sqltext sqltext, v$session sesion
 where sesion.sql_hash_value = sqltext.hash_value
   and sesion.sql_address    = sqltext.address
   and sesion.username is not null
 order by sqltext.piece


-- v$transaction and undo segments
SELECT a.name, b.xacts tr, c.sid, c.serial#, c.username, d.sql_text
FROM   v$rollname a, v$rollstat b, v$session c,
       v$sqltext d,v$transaction e
WHERE  a.usn            = b.usn
and b.usn            = e.xidusn
and c.taddr          = e.addr
and c.sql_address    = d.address
and c.sql_hash_value = d.hash_value
and  rownum < 2
ORDER BY a.name, c.sid, d.piece;

-- v$transaction - undo blocks
SELECT (used_ublk * (SELECT block_size FROM dba_tablespaces WHERE contents = 'UNDO'))/1024/1024 MB
FROM v$transaction
WHERE SES_ADDR='00000003A93027C0'

-- V$TRANSACTION
SELECT SID,SES_ADDR,USERNAME, SUM(used_ublk * (SELECT block_size FROM dba_tablespaces WHERE contents = 'UNDO'))/1024/1024 MB
FROM v$transaction, V$SESSION
wHERE SES_ADDR=SADDR
group by sid, SES_ADDR,USERNAME


UNDO CONSUPTION BY TRANs
========================
SELECT (used_ublk * (SELECT block_size
                                    FROM dba_tablespaces
                                    WHERE contents = 'UNDO'))/1024/1024 MB
FROM v$transaction;



AWR
===
-- Views to check out:
-- DBA_HIST_UNDOSTAT
-- DBA_HIST_SQLTEXT
-- Top 10 UNDO usage SQL

select SQL_TEXT from DBA_HIST_SQLTEXT where SQL_ID in 
   (select distinct MAXQUERYSQLID from 
      (select * from  
         (select SNAP_ID,
                    MAXQUERYLEN,
                    UNDOBLKS,
                    MAXQUERYSQLID, 
                    to_char(BEGIN_TIME,'yyyy/mm/dd hh24:mi') begin, 
                    to_char(END_TIME,'yyyy/mm/dd hh24:mi') end 
          from DBA_HIST_UNDOSTAT 
          order by UNDOBLKS desc, 
                        MAXQUERYLEN desc
         ) where rownum<11
      )
   );