If you have AUTOEXTEND ON it is likely that the datafile had already hit
the MAXSIZE.
So this would result in failure 6 in Oracle Support document " Troubleshooting
ORA-30036 - Unable To Extend Undo Tablespace (Doc ID 460481.1)
Besides dba_hist_tbsp_space_usage you should also look at v$undostat
(also in earlier versions dba_hist_tbsp_space_usage had bugs in reporting
Tablespace Usage for Undo tablespaces)
As John says, if you have AUTOEXTEND ON, Oracle will try to keep extending
Undo retention. Setting a max size for the datafiles and then setting
AUTOEXTEND OFF is better.
Hemant K Chitale
On Mon, Sep 6, 2021 at 2:59 AM Lok P <loknath.73@xxxxxxxxx> wrote:
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@xxxxxxxxx> 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@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.
--
expectations appreciated)
