Find UNDO Usage by Sessions

dbtut

5 years ago

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.

  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;

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.

  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;

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;