• 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. What are Local and Global Indexes in Oracle Database
  2. How to Disable Oracle Net Tracing on a Server Process without Stopping the Process
  3. How to Verify if Oracle Active Data Guard is Enabled
  4. Auto Space Management for Flashback Logs in the Fast Recovery Area – Oracle 19c New Feature
  5. Beginners Guide to Oracle Database Vault
  6. How to Create or Remove Restore Point on Oracle Standby database
  7. Understanding Oracle Database Recyclebin Features and How to Disable it
  8. How to Failover a Service During Instance Shutdown Using SRVCTL
  9. How to Force ASM to Scan the Multipathed Device First using ASMLIB/oracleasm
  10. What is a SQL Cursor

You May Also Like

Primary Sidebar

Recent Posts

  • aws ec2: CLI for AWS EC2 (Command Examples)
  • aws cur – Create, query, and delete AWS usage report definitions (Command Examples)
  • aws configure – Manage configuration for the AWS CLI (Command Examples)
  • aws cognito-idp: Manage Amazon Cognito user pool and its users and groups using the CLI

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright