UNDO Space Error
Date: Sat, 20 Mar 2021 23:52:31 +0530
Message-ID: <CAEjw_fgV9psqr4UmLzbVAVXLHiQegNp2eA7A4J0FV_fHvMFu3g_at_mail.gmail.com>
Hello Listers, Its version 11.2.0.4 of oracle. We suddenly started encountering ORA-30036(ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS2') in one of the databases. We don't have any changes done to the code. We already increased the UNDO tablespace size from ~100Gb to ~190GB but still facing the same and this time we want to find the root cause rather than keep on increasing the size of tablespace.
During one of the failures I saw DBA_UNDO_EXTENTS was showing almost all of the extent status as UNEXPIRED. dba_free_space was showing zero space for that tablespace. We have the data files in the undo tablespace set as autoextend ON. And we are using AUTO undo management with UNDO retention set as 900.
I understand there are two types of UNDO noted by oracle , one is UNDO read which the SELECT query sometimes fails with Ora-01555 (but here we are not encountering that). The other one is UNDO generation because of the DML(INSERT/UPDATE/DELETE) and in this case our failure is because of the same UNDO and each time it's mostly INSERT queries failing while doing data load. So is there any way I can track the exact session/sql/user which is generating maximum UNDO from any historical AWR views and also during run time?
How to debug from history and get the cause of this sudden increase in UNDO space consumption? Any other fix other than increasing UNDO space?
Regards
pap
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Mar 20 2021 - 19:22:31 CET