Undo Tablespace Error
From: Lok P <loknath.73_at_gmail.com>
Date: Sun, 5 Sep 2021 01:35:04 +0530
Message-ID: <CAKna9VYzDJnp22R8E82J2z-u6A4=4n+fD_e0mULQFXLpFuiwOw_at_mail.gmail.com>
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?
Date: Sun, 5 Sep 2021 01:35:04 +0530
Message-ID: <CAKna9VYzDJnp22R8E82J2z-u6A4=4n+fD_e0mULQFXLpFuiwOw_at_mail.gmail.com>
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.
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Sep 04 2021 - 22:05:04 CEST