Re: ORA-01554: transaction concurrency limit reached reason:no undo segment found with available slot params:0, 0 :
Date: Fri, 29 Jun 2018 09:34:54 +0000
Message-ID: <MM1P123MB0842A40C68B7EAAAC5283470A54E0_at_MM1P123MB0842.GBRP123.PROD.OUTLOOK.COM>
Taking a quick guess:
An (automatic) undo segment has 34 slots in the transaction table (the thing in the undo segment header).
If you and up with a small number N of undo segments then you can't have more than 34 * N concurrent transactions active because each one needs a transaction table slot and the next attempt to start a transaction would see ORA-01554
It seems a little unlikely that this would happan in typical system since Oracle would usually end up stealing an extent from an existing undo segment to use it as the first extent of a new undo segment. But it wouldn't be hard to set up a mechanism that started with a small number of undo segments in a relatively small undo tablespace - started some transactions (then leave them idle forever after the first change - e.g. they might have come in from a remote database) that made it impossible to free any extents, then did a lot of work to allocate all the extents (but still left a little space in allocated extents for continuting transactions), then started lots of transactions until you hit the error.
Regards
Hi,
lately I saw the error ORA-01554 and wonder in which circumstances it is possible to hit this error.
Theoretically this is quite clear - the db hit the roof on concurrent transactions, but:
From what I've found it seems the db creates undo segments automatically and if no more space then assigns transactions to existing ones - but this is very imprecise.
Anyone able to elaborate on that?
Regards
Jonathan Lewis
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Remigiusz Sokołowski <remigiusz.sokolowski_at_gmail.com>
Sent: 29 June 2018 10:23:02
To: oracle-l_at_freelists.org
Subject: ORA-01554: transaction concurrency limit reached reason:no undo segment found with available slot params:0, 0 :
- the database (12.1.0.2) is auto undo management
Remigiusz
--
------------------------------------------------------------------------------------
Remigiusz Sokołowski <remigiusz.sokolowski_at_gmail.com<mailto:remigiusz.sokolowski_at_gmail.com>>
------------------------------------------------------------------------------------
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Jun 29 2018 - 11:34:54 CEST