Auslastung/Belegung des Undo Tablespaces anzeigen:

SELECT 
  CASE 
    WHEN STATUS='EXPIRED' THEN 'EXPIRED (reusable)' 
    WHEN STATUS='UNEXPIRED' THEN 'UNEXPIRED (read consistency,
 emergency reuse)'
    WHEN STATUS='ACTIVE' THEN 'ACTIVE (open transactions)' 
    ELSE STATUS 
  END AS STATUS,
  TO_CHAR(ROUND(SUM(bytes) / (1024*1024),
 0),
 '9G999G990') || ' MB' AS SIZE_MB
FROM 
  dba_undo_extents
GROUP BY 
  status
UNION
SELECT 
  'FREE (usable)' AS STATUS,
  TO_CHAR(ROUND((tbs.undo_size- ext.sum_bytes) / (1024*1024),
 0),
 '9G999G990') || ' MB' AS SIZE_MB
FROM
  (SELECT SUM(bytes) sum_bytes FROM dba_undo_extents) ext,
  (SELECT SUM(a.bytes) undo_size FROM dba_tablespaces c
     JOIN v$tablespace b ON b.name = c.tablespace_name
     JOIN v$datafile a ON a.ts# = b.ts#
   WHERE c.contents = 'UNDO'
     AND c.STATUS = 'ONLINE'
  ) tbs;

 

SELECT s.sid, 
       s.username,
       TO_CHAR(ROUND(SUM(ss.value) / 1024 / 1024, 2), '9G999G990D99')  || ' MB' AS SIZE_MB
FROM  v$sesstat ss
  JOIN v$session s ON s.sid = ss.sid
  JOIN v$statname stat ON stat.statistic# = ss.statistic#
WHERE 
  stat.name = 'undo change vector size'
  AND s.type <> 'BACKGROUND'
  AND s.username IS NOT NULL
GROUP BY 
  s.sid, 
  s.username
ORDER BY 
  3 DESC;