Re: Undo Tablespace Error

Thank You John. I understand your point that with AUTOEXTENSIBLE YES oracle may tend to keep the expired UNDO longer to satisfy retention thereby increasing the UNDO tablespace to higher size. But , If we will talk specifically about the exact error here , isn't it true that with AUTOEXTENSIBLE YES, it should not have made it errored out with Ora-30036 rather it would have thrashed/increased UNDO tablespace size by its own?

Also as I see from dba_hist_tbspc_space_usage , the UNDO usage(tablespace_usedsize) was ~160GB which was equal to tablespace_size when it failed with ora-30036 , but then it increased to ~200GB after 4-5hrs, yet to see if somebody has increased it manually or it increased by itself.

On Sun, Sep 5, 2021 at 2:53 AM John Piwowar <jpiwowar_at_gmail.com> wrote:

> The “autoextensible” setting on your undo data files is possibly the
> culprit here. Undo segments are a bit of a special case, and Oracle’s
> decision-making about when to reclaim expired undo can be thrown off when
> it thinks it has the option to extend a data file instead. It’s possible
> that you might still need to resize your undo to accommodate recent changes
> in your workload, but you’re better off with fixed-size undo datafiles,
> followed by an examination of your automatic undo configuration
> (undo_retention, etc).
>
> Sorry about the vagueness of the response; I’m on my phone and can’t get
> decent references with my thumbs. :)
>
> On Sat, Sep 4, 2021 at 1:05 PM Lok P <loknath.73_at_gmail.com> wrote:
>
>> Hello Listers, In one of our 12.1 version databases, a few days back we
>> encountered an error- "Ora-30036 Unable to extend segment by 128 in
>> tablespace 'UNDOTBS1'" for a delete query. This failure has never happened
>> before. And we were trying to see if we really have to increase the size of
>> UNDO tablespace(current size is ~200GB) as it has other impacts like making
>> long running queries run even longer before hitting ora-01555/snapshot too
>> old etc. Or anyway we can track and fix the long transactions which might
>> have consumed large UNDO during that time and then we can try to break that
>> into small transactions?
>>
>> Another thing I notice in the dba_data_files is now showing
>> autoextensible as YES, so how come this query errored out with
>> Ora-30036/space issues?
>>
>> The USED space in dba_hist_tbspc_space_usage for this UNDO tablespace is
>> sometimes showing completely full and sometimes it's a lot empty and i
>> believe it's because of a circular buffer.
>>
>>
>> --
> Regards, John P. (Typed with thumbs on a mobile device. Lowered
> expectations appreciated)
>

--
http://www.freelists.org/webpage/oracle-l