Re: ORA-1555 - 12.2.0.1 Exa

From: John Thomas <jt2354_at_gmail.com>
Date: Tue, 29 Jan 2019 22:01:40 +0000
Message-ID: <CAOHpfbEHkU0jaHixAYY=vbeiQ94fjJn3baOMiGsa3=osm86Y5A_at_mail.gmail.com>



Jack,

You might have a look at the undo advisers, either at the command line or in Enterprise Manager, where the charts may help explain how you're running out of undo space.

https://www.morganslibrary.org/reference/pkgs/dbms_undo_adv.html

Regards,

John Thomas
Database Designer and Administrator
https://oracleexpert.net

On Tue, 29 Jan 2019 at 04:12, Jack van Zanen <jack_at_vanzanen.com> wrote:

> Hi All
>
>
> We are getting ORA-1555 on one of our daily extracts and I need to explain
> to the business why it is happening.
>
> ERROR at line 1:
> ORA-01555: snapshot too old: rollback segment number 92 with name
> "_SYSSMU92_3807070151$" too small
> ORA-06512: at line 43
> ORA-06512: at "SYS.DBMS_SQL", line 1726
> ORA-06512: at line 24
>
>
>
> I have an undo retention of 3 hours (query fails before that generally)
> I have an undo tablespace that can grow to 400G and currently only at 120G
>
> so it does not appear to be the usual suspects so I am thinking of setting
> an event to capture more information to explain it better
>
> would below give me more information why this is happening or is there a
> better solution?
>
> *alter system set events '1555 trace name errorstack level 3';*
>
> My guess is that the same blocks are simply changed too many times during
> the time of query so the rollback record required for CR is simply no
> longer available.
>
>
> Jack van Zanen
>
>
> -------------------------
> This e-mail and any attachments may contain confidential material for the
> sole use of the intended recipient. If you are not the intended recipient,
> please be aware that any disclosure, copying, distribution or use of this
> e-mail or any attachment is prohibited. If you have received this e-mail in
> error, please contact the sender and delete all copies.
> Thank you for your cooperation
>

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jan 29 2019 - 23:01:40 CET

Original text of this message