5 Replies Latest reply: Jan 24, 2013 11:05 PM by sb92075 RSS

    Snapshot Too Old Error

    PrasanthkumarReddy.M
      Hi,

      Today in morning got an below error..

      Snapshot Too Old Error detected: SQL ID 658druv6bzz2h, Snapshot SCN 0x0007.625637b6, Recent SCN 0x0007.739c2dda, Undo Tablespace UNDOTBS1, Current Undo Retention 266916.

      From Alert log:
      -------------------
      ORA-01555 caused by SQL statement below (SQL ID: 658druv6bzz2h, Query Duration=266316 sec, SCN: 0x0007.625637b6):
      select COUNT(REQUEST_ID) AS MissingFaceRecords FROM face_dataquality where FACE_DATAQUALITY.REQUEST_ID in (select REQUEST_ID from message_archive where (REQUEST_TYPE='1' and RETURN_VALUE='1') and CREATION_DATETIME BETWEEN '11-JAN-13 12.00.00.00 AM' and '11-JAN-13 11.59.59.59 PM') and (FACE_QUAL_SCORE =-2)

      Can you guide me how to resolve the above issue.

      Thanks & Regards
      Prasanth
        • 1. Re: Undo tablespace is gettitng full
          sb92075
          SELECT Count(request_id) AS MissingFaceRecords 
          FROM   face_dataquality 
          WHERE  face_dataquality.request_id IN (SELECT request_id 
                                                 FROM   message_archive 
                                                 WHERE  ( request_type = '1' 
                                                          AND return_value = '1' ) 
                                                        AND 
                        creation_datetime BETWEEN '11-JAN-13 12.00.00.00 AM' AND 
                                                  '11-JAN-13 11.59.59.59 PM') 
                 AND ( face_qual_score =- 2 ) 
          Are either face_dataquality or message_archive tables changing (DML with COMMIT)
          while SELECT above is producing the result set?
          • 2. Re: Undo tablespace is gettitng full
            PrasanthkumarReddy.M
            Hi

            Yeah there is change i.e meanwhile DML is happening to the tables.

            We have shell script which we 4 sql queries same as like posted..after getting output of 3 getting the snapshot to error for the 4th query(posted query).

            Thanks & Regards

            Prasanth
            • 3. Re: Undo tablespace is gettitng full
              sb92075
              978154 wrote:
              Hi

              Yeah there is change i.e meanwhile DML is happening to the tables.

              We have shell script which we 4 sql queries same as like posted..after getting output of 3 getting the snapshot to error for the 4th query(posted query).

              Thanks & Regards

              Prasanth
              COMMIT inside LOOP is a direct cause of ORA-01555 error being reported by the SELECT
              • 4. Re: Undo tablespace is gettitng full
                PrasanthkumarReddy.M
                Hi

                How to overcome the issue with out getting ora-01555 error then?

                Thanks & regards
                Prasanth
                • 5. Re: Snapshot Too Old Error
                  Padma....
                  Hi,

                  Snapshot too old error is normally resolved by changing the undo retention time.Also for this retention guarantee has to be ensured.

                  alter tablespace undo retention guarantee;

                  Alter system set undo_retention="........";

                  Regards,
                  Padma...