Oracle PL/SQL/System Tables Views/V Rollname

Query V$ROLLNAME, V$ROLLSTAT

  
SQL> select N.Name,         /* rollback segment name */
  2         S.OptSize       /* rollback segment OPTIMAL size */
  3  from V$ROLLNAME N, V$ROLLSTAT S
  4  where N.USN=S.USN;
                                                              
NAME                               OPTSIZE
------------------------------  ----------
SYSTEM
_SYSSMU1$
_SYSSMU2$
_SYSSMU3$
_SYSSMU4$
_SYSSMU5$
_SYSSMU6$
_SYSSMU7$
_SYSSMU8$
_SYSSMU9$
_SYSSMU10$
SQL>

Users in rollback segments

    

SQL> REM
SQL> column rr heading "RB Segment" format a18
SQL> column us heading "Username" format a15
SQL> column os heading "OS User" format a10
SQL> column te heading "Terminal" format a10
SQL> select R.Name rr,
  2         nvl(S.Username,"no transaction") us,
  3         S.Osuser os,
  4         S.Terminal te
  5    from V$LOCK L, V$SESSION S, V$ROLLNAME R
  6   where L.Sid = S.Sid(+)
  7     and trunc(L.Id1/65536) = R.USN
  8     and L.Type = "TX"
  9     and L.Lmode = 6
 10  order by R.Name
 11  /
SQL>