12 Replies Latest reply on Sep 6, 2013 4:51 PM by jgarry

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


      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 - 64bi

      PL/SQL Release - Production

      CORE Production

      TNS for Solaris: Version - Production

      NLSRTL Version - Production