Newsgroups: comp.databases.oracle.server Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!newsfeed.icl.net!newsfeed.fjserv.net!newsfeed.icl.net!proxad.net!64.233.160.134.MISMATCH!postnews.google.com!news4.google.com!news.glorb.com!sws1!ornl!news.er.usgs.gov!news From: Brian Peasland Subject: Re: UNDOTBS In-Reply-To: <1155003563.008484.263530@i3g2000cwc.googlegroups.com> X-Nntp-Posting-Host: edc-cv-160-54.cr.usgs.gov Content-Type: text/plain; charset=ISO-8859-1; format=flowed Message-ID: User-Agent: Thunderbird 1.5.0.5 (Windows/20060719) Sender: news@igsrsparc2.er.usgs.gov (Janet Walz (GD) x6739) Content-Transfer-Encoding: 7bit Organization: U.S. Geological Survey, Reston VA References: <1155003563.008484.263530@i3g2000cwc.googlegroups.com> Mime-Version: 1.0 Date: Tue, 8 Aug 2006 12:04:50 GMT Lines: 67 Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:272357 aprinsloo@sagetelecom.net wrote: > Oracle 9i env, tbs is in MANUAL management mode and auto extend is off. > > We have a large update script which filled up about 80% of a 20G > UNDOTBS. The script crashed and we plan to reload the schema from a > backup and run the script again, however the UNDOTBS is still sitting > at 80% full. > > Will Oracle know that this rollback data in the UNDOTBS is not required > and start over writing it when we run the update script again, or do we > need to perform some manual intervention to "flush" this data? > > We dont want to rerun the update script and have it fail becaues the > UNDOTBS tablespace fills up. > > Thx in advance. > As Daniel suggested, first ensure that you are using automated undo: SQL> show parameter undo_management NAME TYPE VALUE ---------------------------- ----------- ------------------------------ undo_management string AUTO If the above returns MANUAL, then you are using the old-style rollback segments. Next, verify your undo tablespace: SQL> show parameter undo_tablespace NAME TYPE VALUE ---------------------------- ----------- ------------------------------ undo_tablespace string UNDOTBS Oracle will attempt to keep undo in that tablespace for the number of seconds defined by another parameter: SQL> show parameter undo_retention NAME TYPE VALUE ---------------------------- ----------- ------------------------------ undo_retention integer 3600 So in your case, Oracle will most likely overwrite the undo stored in this tablespace provided it has *expired*, meaning it is older than the undo_retention parameter (specified in seconds). If your undo tablespace is too small, undo will still be overwritten even if the undo has not been expired. You can query V$UNDOSTAT to see if this has ever occurred. HTH, Brian -- =================================================================== Brian Peasland dba@nospam.peasland.net http://www.peasland.net Remove the "nospam." from the email address to email me. "I can give it to you cheap, quick, and good. Now pick two out of the three" - Unknown