DBA_ROLLBACK_SEGS view tips

Oracle Tips by Burleson Consulting

DBA_ROLLBACK_SEGS

<< Return to the index

Oracle 11g makes the following comments about the DBA_ROLLBACK_SEGS table: "Description of rollback segments"

Related notes on DBA_ROLLBACK_SEGS:

Column description of the DBA_ROLLBACK_SEGS view:

SEGMENT_NAME

Description of DBA_ROLLBACK_SEGS.SEGMENT_NAME: "Name of the rollback segment"

OWNER

Description of DBA_ROLLBACK_SEGS.OWNER: "Owner of the rollback segment"

TABLESPACE_NAME

Description of DBA_ROLLBACK_SEGS.TABLESPACE_NAME: "Name of the tablespace containing the rollback segment"

SEGMENT_ID

Description of DBA_ROLLBACK_SEGS.SEGMENT_ID: "ID number of the rollback segment"

FILE_ID

Description of DBA_ROLLBACK_SEGS.FILE_ID: "ID number of the file containing the segment header"

BLOCK_ID

Description of DBA_ROLLBACK_SEGS.BLOCK_ID: "ID number of the block containing the segment header"

INITIAL_EXTENT

Description of DBA_ROLLBACK_SEGS.INITIAL_EXTENT: "Initial extent size in bytes"

NEXT_EXTENT

Description of DBA_ROLLBACK_SEGS.NEXT_EXTENT: "Secondary extent size in bytes"

MIN_EXTENTS

Description of DBA_ROLLBACK_SEGS.MIN_EXTENTS: "Minimum number of extents"

MAX_EXTENTS

Description of DBA_ROLLBACK_SEGS.MAX_EXTENTS: "Maximum number of extents"

PCT_INCREASE

Description of DBA_ROLLBACK_SEGS.PCT_INCREASE: "Percent increase for extent size"

STATUS

Description of DBA_ROLLBACK_SEGS.STATUS: "Rollback segment status"

INSTANCE_NUM

Description of DBA_ROLLBACK_SEGS.INSTANCE_NUM: "Rollback segment owning parallel server instance number"

RELATIVE_FNO

Description of DBA_ROLLBACK_SEGS.RELATIVE_FNO: "Relative number of the file containing the segment header"

DBA_ROLLBACK_SEGS View Source

Oracle 11g's data dictionary defines the DBA_ROLLBACK_SEGS view using the following source query:

select un.name, decode(un.user#,1,'PUBLIC','SYS'),
ts.name, un.us#, f.file#, un.block#,
s.iniexts * ts.blocksize,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
s.extsize * ts.blocksize),
s.minexts, s.maxexts,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
s.extpct),
decode(un.status$, 2, 'OFFLINE', 3, 'ONLINE',
4, 'UNDEFINED', 5, 'NEEDS RECOVERY',
6, 'PARTLY AVAILABLE', 'UNDEFINED'),
decode(un.inst#, 0, NULL, un.inst#), un.file#
from sys.undo$ un, sys.seg$ s, sys.ts$ ts, sys.file$ f
where un.status$ != 1
and un.ts# = s.ts#
and un.file# = s.file#
and un.block# = s.block#
and s.type# in (1, 10)
and s.ts# = ts.ts#
and un.ts# = f.ts#
and un.file# = f.relfile#

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.
You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

Oracle performance Tuning 10g reference poster

Oracle training & performance tuning books