Oracle PL/SQL/System Tables Views/V Rollname

Query V$ROLLNAME, V$ROLLSTAT

   <source lang="sql">
 

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>

 </source>
   
  

Users in rollback segments

   <source lang="sql">
   

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>

 </source>