What is a Rollback Segment?
A rollback segment is made up of multiple extents that consist of several ‘rollback entries’ which keeps undo information for transactions. Rollback segments are needed for:
- Read-consistant database information.
- Database recovery.
- Rolling back uncommitted transactions.
How does Oracle determine which rollback segment to use?
The rules are:
1. Always assign to the rollback segment which has the least number of active transactions.
2. If two or more rollback segments have the same “least number of active txns” , then assign to the one which is after the last one used. This ensures that undo is kept for a longer time.
How to determine which user is using what rollback segment?
Run the below query to determine which user is using the rollback segment:
SELECT r.name "RB NAME ", p.pid "ORACLE PID",
p.spid "SYSTEM PID ", NVL (p.username, 'NO TRANSACTION') "OS USER",
p.terminal
FROM v$lock l, v$process p, v$rollname r, v$session s
WHERE l.sid = s.sid(+)
AND s.paddr = p.addr
AND TRUNC (l.id1(+)/65536) = r.usn
AND l.type(+) = 'TX'
AND l.lmode(+) = 6
ORDER BY r.name;