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
