Re: Undo Tablespace Error

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