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!oleane.net!oleane!nerim.net!proxad.net!64.233.160.134.MISMATCH!postnews.google.com!b28g2000cwb.googlegroups.com!not-for-mail From: "joel garry" Newsgroups: comp.databases.oracle.server Subject: Re: UNDOTBS Date: 8 Aug 2006 15:42:36 -0700 Organization: http://groups.google.com Lines: 40 Message-ID: <1155076956.365464.100790@b28g2000cwb.googlegroups.com> References: <1155003563.008484.263530@i3g2000cwc.googlegroups.com> NNTP-Posting-Host: 67.116.125.178 Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Trace: posting.google.com 1155076961 28643 127.0.0.1 (8 Aug 2006 22:42:41 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Tue, 8 Aug 2006 22:42:41 +0000 (UTC) In-Reply-To: <1155003563.008484.263530@i3g2000cwc.googlegroups.com> User-Agent: G2/0.2 X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0),gzip(gfe),gzip(gfe) X-HTTP-Via: 1.0 ISA2K4-OC1 Complaints-To: groups-abuse@google.com Injection-Info: b28g2000cwb.googlegroups.com; posting-host=67.116.125.178; posting-account=YRNZ5wwAAAAg-yYjMFwy3FyWUbPiwNdq Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:272406 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. See http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:6407993912330 and http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:6894817116500 > > 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. There may be other things, depending on your script. For example, often doing things in SQL rather than PL/SQL, cursor loops, dynamic sql etc. can magically solve "problems." So, why is it a large script? (Personally, I sometimes write ETL scripts that generate huge scripts to do one update at a time, because I can do that faster than figuring out a complicated business rule in SQL. But it all depends. Nologging operations can be much less stressful on the system - but I usually have to deal with difficult-to-recreate standby databases, so I can't use them.) jg -- @home.com is bogus. Santa doesn't get Christmas presents.