Re: Estimating space needed for UNDO tablespaces

That sounds very reasonable. You can check that number against the values in v$undostat as it runs. Remember, UNDO_RETENTION is not guaranteed. If the space is needed by another segment, it may be taken even if the expire time has not been reached.

Daniel

Thomas Jeff wrote:

> Thanks for the reply Dan.
>
> Would you suggest setting UNDO_RETENTION to roughly the length of time of
> the longest
> running job in the database? For example, in our DW, our BI analysts tell
> me that their
> longest batch run is about 1 hr 45 minutes. My uneducated guess is to
> accordingly
> set the parameter to approx 2 hours.
>
> -----Original Message-----
> Sent: Friday, September 26, 2003 4:15 PM
> To: Multiple recipients of list ORACLE-L
>
> That is a good place to start. You might consider adding a little if you
> have many concurrent transactions or want to increase the undo_retention
> to a high number. Once you are using AUM, keep a close eye on
> v$undostat, though there are some known issues with it not populating
> properly, keep an eye on the begin_time and end_time. However, for
> estimation purposes it should work.
>
> Daniel Fink
>
> Thomas Jeff wrote:
>
> > I'm beginning the process of converting over to automatic
> > undo management. I'm wondering as to exactly how large to
> > initially build the UNDO tablespace. Make it roughly
> > the same size as the sum of the current rollback
> > tablespaces? Or has your experience been different,
> > i.e., you've found you've generally needed more or less
> > space with respect to the previous allocation for rollback
> > segments (manual undo)?
> >
> > Thanks.
> >
> > --------------------------------------------
> > Jeffery D Thomas
> > DBA
> > Thomson Information Services
> > Thomson, Inc.
> >
> > Email: jeff.thomas_at_thomson.net
> >
> > Indy DBA Master Documentation available at:
> > http://gkmqp.tce.com/tis_dba
> > --------------------------------------------
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Thomas Jeff
> > INET: jeff.thomas_at_thomson.net
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Thomas Jeff
> INET: jeff.thomas_at_thomson.net
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Daniel Fink
  INET: Daniel.Fink_at_Sun.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



text/x-vcard attachment: Card for Daniel Fink