Re: UNDOTBS

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