Re: ORA-1555 - 12.2.0.1 Exa

Is the extract a single statement or a collection of statements? The alert log should contain the specific statement getting the ORA-1555.

Is the extract doing any data modifications? For example, logging, updating a row to indicate it was extracted, etc. If so, look into a possible 'fetch across commits'.

What other processes are modifying data during the extract? If so, you may need to increase the undo retention to provide Oracle more 'incentive' to use new undo segments instead of re-using existing ones.

On Mon, Jan 28, 2019 at 9:12 PM 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
>

-- 
*Daniel Fink*
Sr. Database Administrator | *Return Path*
m | (303) 808 3282
daniel.fink_at_returnpath.com

[image: Lifecycle Metrics Benchmark]
<http://signatures.returnpath.com/uc/5b731f4d558a8600a895089c>
[image: Powered by Sigstr]
<http://signatures.returnpath.com/uc/5b731f4d558a8600a895089c/watermark>

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