I thought I'd written something about this that I could point you to - but all
I could find by searching my laptop was a copy of a presentation I gave in 2004
which said that if your session can't allocate any more undo space then it will
crash with Oracle error ORA-30036: unable to extend segment by {value} in undo
tablespace '{name}'. It didn't have any comments about when the extents it had
used might be considered freeable available after the crash - and a very big
transaction might take a very long time to rollback and allow its undo space to
be reusable.
Things may have changed since then, of course, and (as of 2004) if you had
enabled a resumable session it would suspend for the resumable timeout rather
than crashing - but then every segment that still had some free space in its
current extents would presumably fill and all the other sessions would either
crash or suspend, depending on whether they were resumable or not). Also, if
the session were resumable and some other undo space became available because
other sessions committed in a pattern that freed some extents, I assume your
transaction could acquire those extents and continue a little longer.
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Anurag Verma <anuragdba@xxxxxxxxx>
Sent: 27 March 2019 02:03
To: ORACLE-L
Subject: Question on UNDO tablespace retention guarantee
If I use the below to avoid "snapshot too old" errors, will it cause any
performance issues to DML statements ?
alter tablespace undotbs1 retention guarantee;
--
For example, if large transactions running and if it consumed all free UNDO
space, will the new DMLs have to wait for getting free UNDO space ?
Thanks,
Anurag Verma
--
//www.freelists.org/webpage/oracle-l
