Hello Listers, In one of our 12.1 version databases, a few days back we
encountered an error- "Ora-30036 Unable to extend segment by 128 in
tablespace 'UNDOTBS1'" for a delete query. This failure has never happened
before. And we were trying to see if we really have to increase the size of
UNDO tablespace(current size is ~200GB) as it has other impacts like making
long running queries run even longer before hitting ora-01555/snapshot too
old etc. Or anyway we can track and fix the long transactions which might
have consumed large UNDO during that time and then we can try to break that
into small transactions?
Another thing I notice in the dba_data_files is now showing autoextensible
as YES, so how come this query errored out with Ora-30036/space issues?
The USED space in dba_hist_tbspc_space_usage for this UNDO tablespace is
sometimes showing completely full and sometimes it's a lot empty and i
believe it's because of a circular buffer.
