|Author's name: Mark D Powell
Author's Email: Mark.Powell@eds.com
|Date written: 4th Sept 2001
Updated: 10th Jan 2002
Oracle version(s): 7.0 - 18.104.22.168
|How do I associate an active session with a rollback segment ?|
Back to index of questions
If you are looking at this FAQ I can think of two different things you are looking for. The first is that you want to assign a session to using a specific rollback segment, which this FAQ will answer. The second is what rollback segment is a session using? This FAQ will also provide the SQL to answer that question.
To assign a session to use a specific rollback segment for a transaction issue the set transaction command:
set transaction use rollback segment roll02;
The set transaction command must be the first statement since the prior commit, rollback, or session creation for it to work; otherwise, you will get an Oracle error: ORA-01453: SET TRANSACTION must be first statement of transaction. To make sure that the command is the first command issued it is common to see the command scripted immediately following a rollback statement.
rollback; set transaction use rollback segment roll02; update big_table set fld1 = 'some value'; commit;
The first commit or rollback ends the transaction and the rollback segment assignment along with it. If you need to assign multiple transactions then the set command has to be re-issued after every commit. Inside pl/sql code you can use the Oracle provided package call dbms_transaction.use_rollback_segment('segname') to set the rollback segment for a transaction.
To find the list of rollback segments available to the database query the dictionary view dba_rollback_segs. Additional information about rollback segments is contained in the dynamic performance views v$rollstat and v$rollname. Because v$rollstat does not contain the segment name join v$rollstat to v$rollname on the usn column for this information.
select segment_name, status from dba_rollback_segs; SEGMENT_NAME STATUS ------------------------------ ---------------- SYSTEM ONLINE ROLL01 ONLINE ROLL02 ONLINE ROLL03 ONLINE ROLL04 ONLINE
The following SQL will show sessions assigned to rollback segments. Note that only transactions are assigned to rollback segments and non-distributed transactions involve a DML operation: insert, update, or delete. So sessions that have issued only normal queries do not show up as being assigned to rollback segments since these sessions will access rollback segments only to read data from them. And if a session needs to read data changed by another session that data can be in any segment.
select s.username, s.sid, rn.name, rs.extents ,rs.status, t.used_ublk, t.used_urec ,do.object_name from v$transaction t ,v$session s ,v$rollname rn ,v$rollstat rs ,v$locked_object lo ,dba_objects do where t.addr = s.taddr and t.xidusn = rn.usn and rn.usn = rs.usn and t.xidusn = lo.xidusn(+) and do.object_id = lo.object_id;
This should answer the question.
Further reading: FAQ Is there a way to detect processes that are rolling back, and can I figure out how long it will take ?
Back to top
Back to index of questions