Brian,
take care, that for the UNDO tablespace AUTOEXTEND is OFF. Otherwise
Oracle allocates new extents beforetrying to overwrite obsolete segments.
Nik
Brian Peasland wrote:
> 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
>
>
>
Received on Wed Aug 09 2006 - 07:19:12 CDT
