Undo Tablespace Analysis in Oracle Database

 Undo Tablespace Analysis in Oracle Database

Last 3 Days Undo Utilization

set linesize 190
SELECT 
    TO_CHAR(begin_time, 'YYYY-MM-DD HH24:MI:SS') AS begin_time,
    TO_CHAR(end_time, 'YYYY-MM-DD HH24:MI:SS') AS end_time,
    a.undoblks AS undo_blocks,
    a.txncount AS transaction_count,
    a.maxquerylen AS max_query_length,
    a.maxqueryid AS max_query_id
FROM 
    (SELECT 
        begin_time,
        end_time,
        SUM(undoblks) AS undoblks,
        SUM(txncount) AS txncount,
        MAX(maxquerylen) AS maxquerylen,
        MAX(maxqueryid) AS maxqueryid
     FROM 
        v$undostat
     WHERE 
        begin_time >= SYSDATE - 3
     GROUP BY 
        begin_time, end_time
    ) a
ORDER BY 
    begin_time;

Currently used Undo TBS

SELECT 
    s.sid,
    s.serial#,
    t.used_ublk AS undo_blocks_used,
    t.start_time,
    s.username,
    s.status
FROM 
    v$session s
JOIN 
    v$transaction t ON s.saddr = t.ses_addr
WHERE 
    s.status = 'ACTIVE';

Undo Utilization specific to Undo Tablespace

SELECT 
    tablespace_name,
    SUM(bytes) / (1024 * 1024) AS used_mb,
    SUM(maxbytes) / (1024 * 1024) AS max_size_mb,
    COUNT(*) AS current_undo_segments
FROM 
    dba_data_files
WHERE 
    tablespace_name = 'UNDOTBS' -- Replace with your actual undo tablespace name
GROUP BY 
    tablespace_name;