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@xxxxxxxxx> 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@xxxxxxxxx> 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
