V$ROLLNAME

V$ROLLNAME lists the names of all online rollback segments. It can only be accessed when the database is open.

Column	Datatype	NULL	Description
USN	NUMBER		Rollback (undo) segment number
NAME	VARCHAR2(30)	NOT NULL	Rollback segment name

Note:

1.       Sample contents of V$ROLLNAME

SQL> select * from  V$ROLLNAME;          USN NAME ---------- ------------------------------          0 SYSTEM          1 _SYSSMU1$          2 _SYSSMU2$          3 _SYSSMU3$          4 _SYSSMU4$          5 _SYSSMU5$          6 _SYSSMU6$          7 _SYSSMU7$          8 _SYSSMU8$          9 _SYSSMU9$         10 _SYSSMU10$   11 rows selected.

2.       Scripts using V$ROLLNAME

-- Online Rollback Segments select d.segment_name, s.extents,        round((s.rssize/1024/1024))||'M',        d.initial_extent, d.next_extent,        d.pct_increase, d.min_extents,        d.max_extents, optsize, d.status from   v$rollname n, v$rollstat s, dba_rollback_segs d where  n.usn = s.usn and    d.segment_name = n.name(+);

-- show active sessions using rollback segments   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;

Oracle data dictionary views

Oracle dynamic performance views

Last updated: 2009-10-27 Tuesday