Re: Undo space error

Thank You so much Nitin. So these steps will give us the transactions that are consuming high UNDO space in real time. But i was trying to understand if there exists any historical views(DBA_HIST*) which i can query which will give us information of any odd transaction/sql_ids in past point in time which must be the cause of those unexpected " ora-30036:" error for the other application queries.

Regards
Pap

On Mon, May 3, 2021 at 1:53 AM Nitin Saxena <ntnsxn7_at_gmail.com> wrote:

> Generally use following to find what is eating up undo:
>
> select s.sql_text from v$sql s, v$undostat u where u.maxqueryid=s.sql_id;
>
> find out most undo used by a session for a currently executing transaction:
> select s.sid,s.username,t.used_urec,t.used_ublk from v$session s,
> v$transaction t where s.saddr = t.ses_addr order by t.used_ublk desc;
>
> Find out which session is currently using the most UNDO,
>
> select s.sid, t.name, s.value from v$sesstat s, v$statname t where
> s.statistic#=t.statistic# and t.name='undo change vector size' order by
> s.value desc;
>
> select sql.sql_text, t.used_urec records, t.used_ublk blocks,
> (t.used_ublk*8192/1024) kb from v$transaction t, v$session s, v$sql sql
> where t.addr=s.taddr and s.sql_id = sql.sql_id and s.username ='&USER_NAME';
>
> Thanks
>
> On Sun, May 2, 2021 at 12:18 PM Pap <oracle.developer35_at_gmail.com> wrote:
>
>> Hi All, it's version 11.2.0.4 of oracle. We are suddenly seeing multiple
>> queries failed with 'ora-30036: unable to extend segment by 8 in undo
>> tablespace 'undotbs1' error even after rerun.
>>
>> In past we have seen lot of ora -01555 for long running queries but so
>> many ora-30036 is seen for the first time. So what must be the cause of
>> this? Is there anyway I can debug the cause of this error from stored
>> historical information in AWR views(dba_hist*)?
>>
>>
>> Regards
>> Pap
>>
>
>
> --
> Cheers
> Nitin
>

--
http://www.freelists.org/webpage/oracle-l