This discussion is archived
5 Replies Latest reply: Jan 24, 2013 9:05 PM by sb92075 RSS

Snapshot Too Old Error

PrasanthkumarReddy.M Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Hi

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

    Thanks & regards
    Prasanth
  • 5. Re: Snapshot Too Old Error
    Padma.... Newbie
    Currently Being Moderated
    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...