How to Find out , Who / What is using your UNDO space?

Below are some views that show information related to undo activity..

* V$UNDOSTAT:                   This shows statistics for 10-minute intervals.

* V$TRANSACTION:             This provides information on current transactions.

* V$SESSTAT:                        Provides individual session statistics, which includes one for undo usage.

V$UNDOSTAT (This provides a hint regarding “who did the thing”)

This  records 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.

Check the below query :

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 (Tells a hint “who uses the undo”)

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# = 255    <-- Which stands for undo change vector size

ORDER BY a.value DESC

Click here to Join Our Blog and Stay Updated