Finding What’s Consuming the Most Undo:
Use the following query to find out which SQL statement has run for the longest time in your database.
SQL> select s.sql_text from v$sql s, v$undostat u
You can join the V$TRANSACTION and the V$SESSION views to find out the most undo used by a session for a
currently executing transaction, as shown here:
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;
You can also issue the following query to find out which session is currently using the most undo in an instance:
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'
order by s.value desc;
The query’s output relies on the statistic undo change vector size in the V$STATNAME view, to show the SID for
the sessions consuming the most undo right now. The V$TRANSACTION view shows details about active transactions.
Here’s a query that joins the V$TRANSACTION, V$SQL and V$SESSION views and shows you the user and the SQL
statement together with the amount of undo space that’s consumed by the SQL statement:
SQL> select sql.sql_text sql_text, t.USED_UREC Records,
(t.USED_UBLK*8192/1024) KBytes from v$transaction t,
where t.addr = s.taddr
and s.sql_id = sql.sql_id
and s.username ='&USERNAME';
The column USED_UREC shows the number of undo records used, and the USED_UBLK column shows the undo
blocks used by a transaction.