Re: UNDO Tablespace

otn.oracle.com

click documentation, books. answer is in the concepts document. The DBA is correct, but the answer could be simplified.

Undo creates before images of transactions. It only captures the before images of 'rows' involved in insert,update, and delete statements. Not all the rows in every table.

This is so that if you choose or if there is a failure you can 'rollback' to the state the database was in before your transaction started. Its also so that you can get a 'consistent' view of the database.

Lets say User A is running an update on Table A that takes 20 minutes. User B comes in and does a select that involves a SUM. User A may get to the first block which has already been updated, then go to a later block that has not been updated, so you get a view of the table that doesnt really exist. Its in transition. The Select statement will go to the rollback to see what the update looks like BEFORE the transaction takes place. This way you either have a select from data before an update takes place or after a commit, after the update, Not half and half.

you probably need to increase the size of your undo tablespace. Just get a hard disk and stick it on there. You can get 37 GB SCSI drives cheap.
>
> From: Sherrie.Kubis_at_swfwmd.state.fl.us
> Date: 2003/08/12 Tue AM 09:39:29 EDT
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Subject: UNDO Tablespace
>
> 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: <rgaffuri_at_cox.net
  INET: rgaffuri_at_cox.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).