Undo Tablespace Usage

Das UNDO-Management ist zentraler Bestandteil für die Sicherstellung der Lesekonsistenz der Oracle-Datenbank. Eigentlich eine gute Sache, aber manchmal hat man auch damit zu kämpfen. Hier ein paar hilfreiche Abfragen, die beim Troubleshooting unterstützen.

-- 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;

-- Belegung des Undo Tablespaces durch User anzeigen

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;

Suche