Re: UNDO Tablespace

Hi!

What exact error message you get? Maybe it's ORA-1555, not running out of free space in undo seg? If you got 1555's, then increase your undo retention to the lenght of longest query. Or redesign.

The DBA is wrong, no physical copies of any tables are made in undo (well, unless you update all columns of all rows in a table), only changes to table are recorded to undo.

Tanel.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, August 12, 2003 4:39 PM

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

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