Obtaining Undo Segments Information

• Data Dictionary Views

– DBA_ROLLBACK_SEGS

• Dynamic Performance Views

– V$ROLLNAME
– V$ROLLSTAT
– V$UNDOSTAT
– V$SESSION
– V$TRANSACTION

To obtain information about all the undo segments in the database

SQL > SELECT segment_name,owner,tablespace_name,status FROM dba_rollback_segs;

The OWNER column specifies the type of an undo segment:
• SYS refers to a private undo segment.
• PUBLIC refers to a public undo segment.

V$ROLLSTAT and V$ROLLNAME

Join the V$ROLLSTAT and V$ROLLNAME views to obtain the statistics of the undo
segments currently used by the instance.

SELECT n.name, s.extents, s.rssize,s.hwmsize, s.xacts, s.status
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;