Oracle7 Server Administrator's Guide

Monitoring Rollback Segment Information

Use the MONITOR ROLLBACK feature of Server Manager/GUI to monitor a rollback segment's size, number of extents, optimal number of extents, activity concerning dynamic deallocation of extents, and current usage by active transaction.

See Also: For a detailed description of how to use the MONITOR for the corresponding operation, see "Set an Optimal Number of Extents for Each Rollback Segment" .

Displaying Rollback Segment Information

The DBA_ROLLBACK_SEGS data dictionary view stores information about the rollback segments of a database. For example, the following query lists the name, associated tablespace, and status of each rollback segment in a database:

SELECT segment_name, tablespace_name, status

   FROM sys.dba_rollback_segs;

SEGMENT_NAME  TABLESPACE_NAME  STATUS

------------- ---------------- ------

SYSTEM        SYSTEM           ONLINE

PUBLIC_RS     SYSTEM           ONLINE

USERS_RS      USERS            ONLINE

In addition, the following data dictionary views contain information about the segments of a database, including rollback segments:

USER_SEGMENTS

DBA_SEGMENTS

Displaying All Rollback Segments

The following query returns the name of each rollback segment, the tablespace that contains it, and its size:

SELECT segment_name, tablespace_name, bytes, blocks, extents

   FROM sys.dba_segments

   WHERE segment_type = 'ROLLBACK';

SEGMENT_NAME TABLESPACE_NAME     BYTES     BLOCKS    EXTENTS

------------ --------------- --------- ---------- ----------

RS1          SYSTEM              20480         10          2

RS2          TS1                 40960         20          3

SYSTEM       SYSTEM             184320         90          3

Displaying Whether a Rollback Segment Has Gone Offline

When you take a rollback segment offline, it does not actually go offline until all active transactions in it have completed. Between the time when you attempt to take it offline and when it actually is offline, its status in DBA_ROLLBACK_SEGS remains ONLINE, but it is not used for new transactions. To determine whether any rollback segments for an instance are in this state, use the following query:

SELECT name, xacts 'ACTIVE TRANSACTIONS'

   FROM v$rollname, v$rollstat

WHERE status = 'PENDING OFFLINE'

   AND v$rollname.usn = v$rollstat.usn;

NAME       ACTIVE TRANSACTIONS

---------- -------------------

RS2                          3

If your instance is part of a Parallel Server configuration, this query displays information for rollback segments of the current instance only, not those of other instances.

Displaying Deferred Rollback Segments

The following query shows which rollback segments are private and which are public. Note that it only displays information about the rollback segments that are currently online for the current instance:

SELECT segment_name, tablespace_name, owner

   FROM sys.dba_rollback_segs;

SEGMENT_NAME  TABLESPACE_NAME  OWNER

------------- ---------------- ------

SYSTEM        SYSTEM           SYS

PUBLIC_RS     SYSTEM           PUBLIC

USERS_RS      USERS            SYS

Displaying All Deferred Rollback Segments

The following query shows all deferred rollback segments (rollback segments that were created to hold rollback entries for tablespaces taken offline until the tablespaces are brought back online):

SELECT segment_name, segment_type, tablespace_name

   FROM sys.dba_segments

WHERE segment_type = 'DEFERRED ROLLBACK';

SEGMENT_NAME   SEGMENT_TYPE           TABLESPACE_NAME

------------   ------------           ---------------

USERS_RS       DEFERRED ROLLBACK      USERS