DBA_ROLLBACK_SEGS

DBA_ROLLBACK_SEGS describes rollback segments.

Column	Datatype	NULL	Description
SEGMENT_NAME	VARCHAR2(30)	NOT NULL	Name of the rollback segment
OWNER	VARCHAR2(6)		Owner of the rollback segment
TABLESPACE_NAME	VARCHAR2(30)	NOT NULL	Name of the tablespace containing the rollback segment
SEGMENT_ID	NUMBER	NOT NULL	ID number of the rollback segment
FILE_ID	NUMBER	NOT NULL	File identifier number of the file containing the segment head
BLOCK_ID	NUMBER	NOT NULL	ID number of the block containing the segment header
INITIAL_EXTENT	NUMBER		Initial extent size in bytes
NEXT_EXTENT	NUMBER		Secondary extent size in bytes
MIN_EXTENTS	NUMBER	NOT NULL	Minimum number of extents
MAX_EXTENTS	NUMBER	NOT NULL	Maximum number of extent
PCT_INCREASE	NUMBER		Percent increase for extent size
STATUS	VARCHAR2(16)		Rollback segment status
INSTANCE_NUM	VARCHAR2(40)		Rollback segment owning Real Application Clusters instance number
RELATIVE_FNO	NUMBER	NOT NULL	Relative file number of the segment header

Note:

1.       In automatic undo management (AUM) DBA's don't have privileges to offline/online undo segments. And this is controlled by SMON process. So the view will be useful only in few scenarios, where we have internal errors with undo segments.

2.       Scripts using DBA_ROLLBACK_SEGS

-- All Rollback Segments select segment_name, owner, tablespace_name, name from   dba_rollback_segs d, v$datafile f where  d.file_id = f.file#;

-- 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(+);

Oracle data dictionary views

Oracle dynamic performance views

Last updated: 2009-10-27 Tuesday