How do I associate an active session with a rollback segment ?

The Oracle (tm) Users' Co-Operative FAQ

How do I associate an active session with a rollback segment ?

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 ?