Re: UNDO Tablespace

Sherrie,

Also check out Tim Gorman's paper/presentation "Cat's, Dog's and ORA-1555s" on his website www.evdbt.com. It is a great place to start, then move on to my papers which are more geeky in nature (reading block dumps, following the undo chain, etc.).

Daniel Fink

(Gotta run, just checking the email before the Hotsos Clinic. YEAH!)

Rachel Carmichael wrote:
>
> Sherrie,
>
> First, the Oracle documentation does a pretty good job of explaining
> how undo works. check out the concepts manual.
>
> Second, you might want to read Dan Fink's papers on Undo Internals, you
> can find them on his website at
>
> http://www.optimaldba.com/library.html
>
> He does a very good job of explaining how undo works.
>
> Lastly, I would verify that the vendor's DBA has actually worked with
> Oracle before. Oracle DOES NOT make a copy of the entire physical table
> for every table involved in every query, nor does it hold onto those
> copies for the length of the retention period.
>
> Retention period is misleading, as Oracle WILL overwrite information in
> an undo segment, even if the retention time has not been reached, if
> space is needed. Dan does an excellent job of explaining that.
>
> He's off at the Hotsos Clinic now (lucky man!) or he'd be answering
> this himself
>
> --- Sherrie.Kubis_at_swfwmd.state.fl.us wrote:
> > I have a 2gb UNDO tablespace. A third-party application continually
> > runs
> > out of UNDO when it joins two tables to produce a result table. Our
> > retention time is set to 15 minutes, the NoSpaceErrCnt in V$UNDOSTAT
> > is
> > always zero. The answer for this from our vendor is to increase the
> > size
> > of the UNDO based on their DBAs statement that UNDO is consumed
> > rapidly
> > because every query makes a physical copy of all tables and holds on
> > to
> > them for the retention period. I can find nothing that discusses
> > exactly
> > how UNDO physically works, and am not sure that this can be true.
> > That
> > would mean that every user querying our database would have copies of
> > the
> > tables in the UNDO, and I'd need about a gazillion gb to handle that.
> > Does
> > anyone have any insights on how the UNDO physically works?
> >
> >
> >
> > ---------------
> > Sherrie Kubis
> > Southwest Florida Water Management District
> > 2379 Broad Street
> > Brooksville FL 34604-6899
> >
> > Phone: (352) 796-7211, Ext. 4033
> > Fax: (352) 754-6776
> > Email: Mailto:Sherrie.Kubis_at_swfwmd.state.fl.us
> > http://WaterMatters.org
> >
> >
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author:
> > INET: Sherrie.Kubis_at_swfwmd.state.fl.us
> >
> > 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).
>
> __________________________________
> Do you Yahoo!?
> Yahoo! SiteBuilder - Free, easy-to-use web site design software
> http://sitebuilder.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Rachel Carmichael
> INET: wisernet100_at_yahoo.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).

-- 
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