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
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@xxxxxxxxx> wrote:
Hello Listers, In one of our 12.1 version databases, a few days back weRegards, John P. (Typed with thumbs on a mobile device. Lowered
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.