Which query/process is using most of the UNDO tablespace?

Some very nice scripts found at:

http://www.dbaref.com/home/dba-routine-tasks/findingwhatsconsumingthemostundo


SQL> select s.sql_text from v$sql s, v$undostat u
where u.maxqueryid=s.sql_id;

You can also use following SQL to find out most undo used by a session for a currently executing transaction.

SQL> select s.sid,s.username,t.used_urec,t.used_ublk
from v$session s, v$transaction t
where s.saddr = t.ses_addr
order by t.used_ublk desc;

To find out which session is currently using the most UNDO,

SQL>select s.sid, t.name, s.value
from v$sesstat s, v$statname t
where s.statistic#=t.statistic#
and t.name='undo change vector size'

and s.value > 0
order by s.value;

SQL>select sql.sql_text, t.used_urec records, t.used_ublk blocks,
(t.used_ublk*8192/1024) kb from v$transaction t,
v$session s, v$sql sql
where t.addr=s.taddr
and s.sql_id = sql.sql_id
and s.username ='&USERNAME';