• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How to determine which user is using what rollback segment?

by admin

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;

Filed Under: oracle

Some more articles you might also be interested in …

  1. ORA-00904: invalid identifier
  2. oracleasm Command Examples (Cheat Sheet)
  3. How To Calculate The Required Network Bandwidth Transfer Of Redo In Data Guard Environments
  4. What is Thread Dump in WebLogic
  5. How to move a Datafile to a different Location on a Physical Standby Database
  6. Script to Automatically Start GoldenGate
  7. MUTATING Table Error and How to Resolve it (ORA-04091)
  8. ORA-28007: the password cannot be reused
  9. How to Drop Undo Tablespace in Oracle Database
  10. Real-time SQL Monitoring for Developers – Oracle Database 19c New Feature

You May Also Like

Primary Sidebar

Recent Posts

  • JavaFX ComboBox: Set a value to the combo box
  • Nginx load balancing
  • nginx 504 gateway time-out
  • Images preview with ngx_http_image_filter_module

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright