Undo Space Data Dictionary Views

This section lists views that are useful for viewing information about undo space in the automatic undo management mode and provides some examples. In addition to views listed here, you can obtain information from the views available for viewing tablespace and datafile information. See "Datafiles Data Dictionary Views" for information on getting information about those views.

The following dynamic performance views are useful for obtaining space information about the undo tablespace:

View	Description
`V$UNDOSTAT`	Contains statistics for monitoring and tuning undo space. Use this view to help estimate the amount of undo space required for the current workload. The database also uses this information to help tune undo usage in the system. This view is meaningful only in automatic undo management mode.
`V$ROLLSTAT`	For automatic undo management mode, information reflects behavior of the undo segments in the undo tablespace
`V$TRANSACTION`	Contains undo segment information
`DBA_UNDO_EXTENTS`	Shows the status and size of each extent in the undo tablespace.
`DBA_HIST_UNDOSTAT`	Contains statistical snapshots of `V$UNDOSTAT` information. See Oracle Database 2 Day DBA for more information.

See Also:

Oracle Database Reference for complete descriptions of the views used in automatic undo management mode

The V$UNDOSTAT view is useful for monitoring the effects of transaction execution on undo space in the current instance. Statistics are available for undo space consumption, transaction concurrency, the tuning of undo retention, and the length and SQL ID of long-running queries in the instance.

Each row in the view contains statistics collected in the instance for a ten-minute interval. The rows are in descending order by the BEGIN_TIME column value. Each row belongs to the time interval marked by (BEGIN_TIME, END_TIME). Each column represents the data collected for the particular statistic in that time interval. The first row of the view contains statistics for the (partial) current time period. The view contains a total of 576 rows, spanning a 4 day cycle.

The following example shows the results of a query on the V$UNDOSTAT view.

SELECT TO_CHAR(BEGIN_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME,
         TO_CHAR(END_TIME, 'MM/DD/YYYY HH24:MI:SS') END_TIME,
         UNDOTSN, UNDOBLKS, TXNCOUNT, MAXCONCURRENCY AS "MAXCON"
         FROM v$UNDOSTAT WHERE rownum <= 144;
  
  BEGIN_TIME          END_TIME               UNDOTSN   UNDOBLKS   TXNCOUNT     MAXCON
  ------------------- ------------------- ---------- ---------- ---------- ----------
  10/28/2004 14:25:12 10/28/2004 14:32:17          8         74   12071108          3
  10/28/2004 14:15:12 10/28/2004 14:25:12          8         49   12070698          2
  10/28/2004 14:05:12 10/28/2004 14:15:12          8        125   12070220          1
  10/28/2004 13:55:12 10/28/2004 14:05:12          8         99   12066511          3
  ...
  10/27/2004 14:45:12 10/27/2004 14:55:12          8         15   11831676          1
  10/27/2004 14:35:12 10/27/2004 14:45:12          8        154   11831165          2
 
  144 rows selected.

The preceding example shows how undo space is consumed in the system for the previous 24 hours from the time 14:35:12 on 10/27/2004.