You are hereWho is using your UNDO space?
Who is using your UNDO space?
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...
Do you want to assist the 2012 OTN Latinamerican Tour? More info here OTN LAD Tour 2012
Are you Argentinian and interested on MySQL/NoSQL/Cloud? This event is for you Primer conferencia de usuarios de MySQL / NoSQL & Cloud para América Latina