Re: UNDO Space Error

Thank You Sayan.

I checked using the below query during run time, but don't see any one specific session coming out as one of suspects. But I will try to see it again when the issue occurs. But another doubt I have is , if there exists any historical AWR view matching v$transaction which will help me go back and check the exact snap times(when failure occurred) to see what exact session/process is the culprit and find the cause?

select b.addr,

  a.sid,

  a.username,

  b.xidusn,

  b.used_urec,

  (b.used_ublk*8192)/1024 size_mb

from

  gv$session a,

  gv$transaction b

where

  a.saddr = b.ses_addr

  and a.inst_id=b.inst_id

order by b.used_ublk desc nulls last

On Sat, Mar 20, 2021 at 11:56 PM Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:

> 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