Rollback/Undo Segment Usage Analysis (vrolluse.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script analyzes active rollback/undo segment usage by:

• Identifying all active transactions
• Showing which undo segments they’re using
• Providing session and process information
• Calculating transaction duration
• Sorting by longest-running transactions first

Script

rem  vrolluse.sql
rem
ttitle 'Rollback Segment Users'
set linesize 600
col usn                 format 99       heading 'ID'
col name                format a15      heading 'SEGMENT'
col schemaname          format a12      heading 'USERNAME'
col module    format a20  heading 'MODULE'
col pid                 format 9999     heading 'PID'
col sid                 format 9999     heading 'SID'
col osuser              format a10      heading 'OSUSER'
col spid                format 99999    heading 'OSPID'
col held_time           format 9999999  heading 'HELD TIME|(SECS)'
col object_name    format a24  heading 'Object'

select r.usn,
       r.name,
       s.schemaname,
       s.module,
       s.sid,
       p.pid,
       s.osuser,
       p.spid,
       used_ublk,
       -- nvl(O.Owner||'.'||O.Object_Name,to_char(LO.Object_Id)) Object_Name,
       (sysdate - to_date(t.start_time,'mm/dd/yy hh24:mi:ss'))*60*60*24 held_time
from   v$transaction t,
       v$session s,
       v$process p,
       v$rollname r
       --, v$locked_object lo,
       -- dba_objects o
where  t.xidusn = r.usn
and    t.addr   = s.taddr
and    s.paddr  = p.addr
-- and    lo.xidusn = r.usn
-- and    lo.session_id = s.sid
-- and    o.object_id(+) = lo.object_id
order by (sysdate - to_date(t.start_time,'mm/dd/yy hh24:mi:ss'))*60*60*24 desc
/

Usage

SQL> @vrolluse.sql

Required Privileges

• SELECT on V$TRANSACTION
• SELECT on V$SESSION
• SELECT on V$PROCESS
• SELECT on V$ROLLNAME

Sample Output

Rollback Segment Users

ID SEGMENT         USERNAME     MODULE               SID  PID OSUSER     OSPID USED_UBLK HELD TIME
                                                                                           (SECS)
-- --------------- ------------ -------------------- ---- ---- ---------- ----- --------- ---------
 3 _SYSSMU3_12345$ SCOTT        SQL*Plus              156   45 oracle     12345       234     3,456
 7 _SYSSMU7_67890$ HR           HR_BATCH_JOB          234   67 hruser     23456     1,234     1,234
 2 _SYSSMU2_34567$ PAYROLL      SALARY_PROCESS        512   89 payroll    34567     5,678       567
 5 _SYSSMU5_45678$ INVENTORY    sqlplus@prod01         78   23 invuser    45678        12        45

Key Output Columns

• ID: Undo segment number
• SEGMENT: Undo segment name
• USERNAME: Database username
• MODULE: Application module name
• SID: Session identifier
• PID: Oracle process ID
• OSUSER: Operating system user
• OSPID: Operating system process ID
• USED_UBLK: Number of undo blocks used
• HELD TIME (SECS): Transaction duration in seconds

Understanding the Metrics

Transaction Duration

• Normal: < 60 seconds for OLTP
• Warning: 300-600 seconds
• Critical: > 600 seconds (10+ minutes)
• Long transactions can cause ORA-01555

Undo Block Usage

• High USED_UBLK indicates large transactions
• Monitor for undo tablespace pressure
• Consider transaction size optimization

Common Issues

1. Long-Running Transactions

   • Block undo space reuse
   • Increase snapshot too old risk
   • May indicate missing commits

2. Large Transactions

   • High undo block consumption
   • Potential performance impact
   • Consider batch size reduction

Common Use Cases

1. Troubleshooting ORA-01555

   • Identify long-running queries
   • Find uncommitted transactions
   • Plan undo retention tuning

2. Performance Analysis

   • Detect blocking transactions
   • Find resource-intensive operations
   • Monitor batch job impact

3. Space Management

   • Track undo usage patterns
   • Plan undo tablespace sizing
   • Identify space consumers

Advanced Analysis

To include locked objects (uncomment in script):

-- Shows which objects are being modified
nvl(O.Owner||'.'||O.Object_Name,to_char(LO.Object_Id)) Object_Name

To check undo tablespace usage:

SELECT tablespace_name,
       ROUND(SUM(bytes)/1024/1024) used_mb,
       ROUND(SUM(maxbytes)/1024/1024) max_mb
FROM dba_undo_extents
WHERE status = 'ACTIVE'
GROUP BY tablespace_name;

Best Practices

1. Application Design

   • Commit frequently in batch operations
   • Avoid long-running transactions
   • Use appropriate isolation levels

2. Monitoring

   • Alert on transactions > 10 minutes
   • Track undo usage trends
   • Monitor for blocking scenarios

3. Tuning

   • Size undo appropriately
   • Set undo_retention based on needs
   • Consider undo advisor recommendations

Related Scripts

• Transaction Analysis
• Lock Analysis
• Session Analysis