Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: question regarding automatic undo

RE: question regarding automatic undo

From: Goulet, Dick <>
Date: Tue, 21 Jun 2005 13:09:02 -0400
Message-ID: <>


    Yes the database does things like that. It will create additional segments as needed, offline them when apparently no longer needed until they are no longer referenced, see undo_retention, and then drop them. It's perfectly normal behavior. Check out V$UNDOSTAT for further information. Oh heck, I'll be nice:     

V$UNDOSTAT V$UNDOSTAT displays a histogram of statistical data to show how well the system is working. The available statistics include undo space consumption, transaction concurrency, and length of queries executed in the instance. You can use this view to estimate the amount of undo space required for the current workload. Oracle uses this view to tune undo usage in the system. This view is available in both automatic undo management mode and manual undo management mode.

Each row in the view keeps statistics collected in the instance for a 10-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 1008 rows, spanning a 7 day cycle.

Column Datatype Description
BEGIN_TIME DATE Identifies the beginning of the time interval

END_TIME DATE Identifies the end of the time interval

UNDOTSN NUMBER Represents the last active undo tablespace in the duration of time. The tablespace ID of the active undo tablespace is returned in this column. If more than one undo tablespace was active in that period, the active undo tablespace that was active at the end of the period is reported.

UNDOBLKS NUMBER Represents the total number of undo blocks consumed. You can use this column to obtain the consumption rate of undo blocks, and thereby estimate the size of the undo tablespace needed to handle the workload on your system.

TXNCOUNT NUMBER Identifies the total number of transactions executed within the period

MAXQUERYLEN NUMBER Identifies the length of the longest query (in number of seconds) executed in the instance during the period. You can use this statistic to estimate the proper setting of the UNDO_RETENTION initialization parameter.

MAXCONCURRENCY NUMBER Identifies the highest number of transactions executed concurrently within the period

UNXPSTEALCNT NUMBER Number of attempts to obtain undo space by stealing unexpired extents from other transactions

UNXPBLKRELCNT NUMBER Number of unexpired blocks removed from certain undo segments so they can be used by other transactions

UNXPBLKREUCNT NUMBER Number of unexpired undo blocks reused by transactions

EXPSTEALCNT NUMBER Number of attempts to steal expired undo blocks from other undo segments

EXPBLKRELCNT NUMBER Number of expired undo blocks stolen from other undo segments

EXPBLKREUCNT NUMBER Number of expired undo blocks reused within the same undo segments

SSOLDERRCNT NUMBER Identifies the number of times the error ORA-01555 occurred. You can use this statistic to decide whether or not the UNDO_RETENTION initialization parameter is set properly given the size of the undo tablespace. Increasing the value of UNDO_RETENTION can reduce the occurrence of this error.

NOSPACEERRCNT NUMBER Identifies the number of times space was requested in the undo tablespace and there was no free space available. That is, all of the space in the undo tablespace was in use by active transactions. The corrective action is to add more space to the undo tablespace.

From: Jeffrey Beckstrom [] Sent: Tuesday, June 21, 2005 1:03 PM
To:;; ORACLE-L_at_IC.SUNYSB.EDU;; Goulet, Dick;
Subject: RE: question regarding automatic undo

Am I correct in that possibly database 2 was very busy and decided it needed more segment so dynamically created them. Sometime thereafter, not needed so they were offlined????

>>> "Goulet, Dick" <> 6/21/05 12:54:27 PM >>>

Yes. When you enable automatic undo management you leave all of that to the database engine.

[] On Behalf Of Jeffrey Beckstrom Sent: Tuesday, June 21, 2005 12:41 PM
Subject: question regarding automatic undo

Two identical databases.  

database 1 has 10 undo online and 2 offline database 2 has 10 undo online and 23 offline  

Why does the second have more offline undo segments. Does Oracle automatically create them if needed and when no longer needed offline it??      

Jeffrey Beckstrom
Database Administrator
Greater Cleveland Regional Transit Authority 1240 W. 6th Street
Cleveland, Ohio 44113

Received on Tue Jun 21 2005 - 13:14:33 CDT

Original text of this message