RE: UNDO Tablespace

Given the answer you got, you can bet that their application must be fun to look at ... Undo space is basically born-again rollback segments; look for Dan Fink's papers on the topic, you'll probably find all the answers you need there.

I presume that you must be getting something like ORA-1555. If this is right, basically, your join of death runs while updates are concurrently taking place (and committed). As a result, when your select needs the data as it was when it started, it has to look for them in the undo tablespace. After 15mn it's no longer here. First thing to do would probably be to make that query runtime fall below 15mn. If rewriting it is out of question, try outlines.

>----- ------- Original Message ------- -----
>From: Sherrie.Kubis_at_swfwmd.state.fl.us
>To: Multiple recipients of list ORACLE-L
><ORACLE-L_at_fatcity.com>
>Sent: Tue, 12 Aug 2003 05:39:29
>
>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?
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriolecorp.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).