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