This discussion is archived
12 Replies Latest reply: Sep 6, 2013 9:51 AM by jgarry RSS

Diagnosing ORA-01555: what table had the unreconstructable block?

mtefft Journeyer
Currently Being Moderated

We have some very long-running batch processes (i.e. > 20 hours for a single INSERT statement) and we sometimes get  ORA-01555 (snapshot too old) failures. Our undo_retention is 10800 seconds=3 hours.

 

We have discussed (with the development team) the combination of events that are needed for this situation to arise. They are unable to think of a situation where any of the tables (in today's incident, there are 7 of them) would have been updated after this job started.

 

Is there a method for analyzing the trace file to determine which table had the block that could not be reconstructed due to overwritten UNDO? This might give us ideas for how to avoid this via operational changes (i.e. changing the job schedule).

 

Of course, we are also pursuing tuning efforts for this query.

 

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

PL/SQL Release 10.2.0.5.0 - Production

CORE 10.2.0.5.0 Production

TNS for Solaris: Version 10.2.0.5.0 - Production

NLSRTL Version 10.2.0.5.0 - Production

 

Thanks,

Mike

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points