Re: ORA-1555 - 12.2.0.1 Exa
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-lReceived on Tue Jan 29 2019 - 23:01:40 CET