ORA-01554: transaction concurrency limit reached reason:no undo segment found with available slot params:0, 0 :

From: Remigiusz Sokołowski <remigiusz.sokolowski_at_gmail.com>
Date: Fri, 29 Jun 2018 11:23:02 +0200
Message-ID: <CACLSTZmPPB4_agvgdufLVhRNPCuOXi-E7hF_q+9M8mCzymOGyg_at_mail.gmail.com>



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_at_gmail.com>
------------------------------------------------------------------------------------

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Jun 29 2018 - 11:23:02 CEST

Original text of this message