Sure you've faced this situation: a growing undo tablespace, that seems it could engulf your entire disk space... until finally stops demanding additional space, and within some minutes (or hours, depends on your UNDO_RETENTION setting) you start to see more and more free space in your tablespace. If you scratched your head wondering 'what happened?' or 'who the User did this?', this post may be helpful.
There are some views that show information related to undo activity:
* V$UNDOSTAT: histogram-like view that shows statistics for 10-minute intervals.
* V$TRANSACTION: present time view providing information on current transactions.
* V$SESSTAT: individual session statistics, which includes one for undo usage.
V$UNDOSTAT will provide a who did hint, recording the longest running query for that 10-interval, through the MAXQUERYID column which may be linked to V$SQL and use columns PARSING_USER_ID or PARSING_SCHEMA_NAME the get a grip on the suspect.
V$TRANSACTION linked with V$SESSION will show current used undo blocks for ongoing transactions. This query may help:
SELECT a.sid, a.username, b.used_urec, b.used_ublk FROM v$session a, v$transaction b WHERE a.saddr = b.ses_addr ORDER BY b.used_ublk DESC
V$SESSTAT provides another view, a who uses the undo kind of view, but we must avoid to get lost in the maze of Oracle statistics and focus on just one: Undo change vector size, which will accumulate the bytes of undo used during the session lifetime. Following query is designed to pinpoint who is having a high undo activity.
SELECT a.sid, b.name, a.value FROM v$sesstat a, v$statname b WHERE a.statistic# = b.statistic# AND a.statistic# = 176 <-- Which stands for undo change vector size ORDER BY a.value DESC
Good luck with your UNDO-eating monsters...
Nov 25th 2011: I have updated these scripts, please read Who is using your UNDO space? Improved Script"
You may be interested on Exadata related posts, if so then below is a list of them:
My steps to implement Exadata Hybrid Columnar Compression (EHCC)
Subscribe to Oracle Database Disected by Email