Re: UNDO Space Error

Hi Pap,

Have a look at V$transaction

--
Best regards,
Sayan Malakshinov
Oracle performance tuning expert
Oracle Database Developer Choice Award winner
Oracle ACE Associate
http://orasql.org

сб, 20 мар. 2021 г., 21:22 Pap <oracle.developer35_at_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-l