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
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Remigiusz Sokołowski <remigiusz.sokolowski@xxxxxxxxx>
Sent: 29 June 2018 10:23:02
To: oracle-l@xxxxxxxxxxxxx
Subject: ORA-01554: transaction concurrency limit reached reason:no undo
segment found with available slot params:0, 0 :
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:
- the database (12.1.0.2) is auto undo management
- transactions are set to 1647, transactions_per_rollback_segment to 5, but I
believe those are simply default settings and according to docs those settings
are ignored for automatic undo management
- in docs it is stated the undo on AUTO is limited mostly by the undo
tablespace size (but here this size was not depleted - overall there are
~524000 blocks with ~64000 used between midnight and 15 when the error was hit
according to v$undostat and maximum query lasting below 1h)
- also I would rather expect ORA-01652 on undo tablespace
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
Remigiusz
--
------------------------------------------------------------------------------------
Remigiusz Sokołowski
<remigiusz.sokolowski@xxxxxxxxx<mailto:remigiusz.sokolowski@xxxxxxxxx>>
------------------------------------------------------------------------------------
--
//www.freelists.org/webpage/oracle-l
