Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
You can find the undo usage of a session with the following query. Historical information is also available.
You can find the undo usage of all sessions or active sessions by changing the conditions in the query.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | SELECT r.NAME "Undo Segment Name", dba_seg.size_mb, DECODE (TRUNC (SYSDATE - LOGON_TIME), 0, NULL, TRUNC (SYSDATE - LOGON_TIME) || ' Days' || ' + ') || TO_CHAR ( TO_DATE (TRUNC (MOD (SYSDATE - LOGON_TIME, 1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON, v$session.SID, v$session.SERIAL#, p.SPID, v$session.process, v$session.USERNAME, v$session.STATUS, v$session.OSUSER, v$session.MACHINE, v$session.PROGRAM, v$session.module, action FROM v$lock l, v$process p, v$rollname r, v$session, ( SELECT segment_name, ROUND (bytes / (1024 * 1024), 2) size_mb FROM dba_segments WHERE segment_type = 'TYPE2 UNDO' ORDER BY bytes DESC) dba_seg WHERE l.SID = p.pid(+) AND v$session.SID = l.SID AND TRUNC (l.id1(+) / 65536) = r.usn AND l.TYPE(+) = 'TX' AND l.lmode(+) = 6 AND r.NAME = dba_seg.segment_name --AND v$session.username = 'ADURUOZ' --AND status = 'ACTIVE' ORDER BY size_mb DESC; |
The following query also provides summary information.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SELECT u.tablespace_name tablespace, s.username, u.status, SUM (u.bytes) / 1024 / 1024 sum_in_mb, COUNT (u.segment_name) seg_cnts FROM dba_undo_extents u LEFT JOIN v$transaction t ON u.segment_name = '_SYSSMU' || t.xidusn || ' [the_ad id="5304"] LEFT JOIN v$session s ON t.addr = s.taddr GROUP BY u.tablespace_name, s.username, u.status ORDER BY 1, 2, 3; |
