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

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

    mtefft

      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