Re: Question on UNDO tablespace retention guarantee
Date: Wed, 27 Mar 2019 09:21:54 +0000
Message-ID: <LO2P265MB0415B4D18E4A1D7865CDB283A5580_at_LO2P265MB0415.GBRP265.PROD.OUTLOOK.COM>
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
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,
--
Jonathan Lewis
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Anurag Verma <anuragdba_at_gmail.com>
Sent: 27 March 2019 02:03
To: ORACLE-L
Subject: Question on UNDO tablespace retention guarantee
--
Anurag Verma
http://www.freelists.org/webpage/oracle-l
Received on Wed Mar 27 2019 - 10:21:54 CET