Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2003
    Posts
    15

    Unanswered: ORA-01555: snapshot too old: rollback segment number 2 with name "R01" too small

    Hello,

    ORA-01555: snapshot too old: rollback segment number 2 with name "R01" too small

    Could you please tell me the reason for the above error? I suppose it is thrown when a transaction is running for a long time. I reduced the amount of data to be processed. But still the same error is coming.

    TIA,
    Sankar

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Go to http://asktom.oracle.com and on ORA-01555
    Generally speaking this happens when doing too frequent COMMITs.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    you could increase the size of the rollback, or add a large rollback segment in and disable all the others whilst you run your big query. If you are inserting in a loop you could commit every n rows eg
    IF mod(cursor%ROWCOUNT,100) = 0 THEN
    commit;
    END IF;

  4. #4
    Join Date
    Jan 2008
    Posts
    14
    Quote Originally Posted by anacedent View Post
    Go to Ask Tom Home and on ORA-01555
    Generally speaking this happens when doing too frequent COMMITs.
    Dont you mean too few commits?

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    No, too many.

    This happens, for example, when people put commit into a loop. Something like
    Code:
    for cur_r in (select empno, sal from emp) loop
      update some_table set
        salary = cur_r.sal
        where employee_no = cur_r.empno;
      commit;
    end loop;

  6. #6
    Join Date
    Feb 2012
    Posts
    1

    ERROR at line 1:ORA-01555: snapshot too old: rollback segment number 25 with name "RB

    Snapshot too old - Oracle8i Enterprise Edition Release 8.1.7.4.0

    Hi i have the same problem and this happens any time (Morning or night), i read that we can change de rollback segment but it will be difficult almost imposible to set something in the Database.

    let me show you what is my query.

    Llenando la tabla TMPF0911AUX...
    1 DECLARE
    2 CURSOR AUXcursor IS
    3 SELECT glaid, -- se agrega nueva linea para la segunda sección con la F0901
    4 glco,
    5 glmcu,
    6 globj,
    7 glsub,
    8 --gmr020,
    9 TO_DATE('01-JAN-' || TO_CHAR(1900 + SUBSTR(gldgj, 1, 3))) + SUBSTR(gldgj, -3, 3) - 1 fecha,
    10 --abac29,
    11 glsbl,
    12 gllt,
    13 glctry,
    14 glfy,
    15 glpn,
    16 SUM(-1 * glaa) AS glaa
    17 FROM PRODDTA.F0911 T0911
    18 --PRODDTA.F0901 T0901,
    19 --PRODDTA.F0101 T0101
    20 WHERE --GLDCT='IA' AND GLDOC=1 AND GLKCO='00001'
    21 --glaid = gmaid
    22 --AND glco = aban8
    23 --AND
    24 gllt = 'AA'
    25 AND globj BETWEEN '40000' AND '90000'
    26 AND glpost = 'P'
    27 --and glco='00002' and glmcu=' 2001' and globj='50503' and glsub='01'
    28 AND glaa <> 0
    29 AND GLDGJ >= (select dtbgdate from proddta.f57dts01 where rtrim(dtdl01)='VECOGA_FINANZAS')
    30 AND GLDGJ <= (select dtendate from proddta.f57dts01 where rtrim(dtdl01)='VECOGA_FINANZAS')
    31 GROUP BY glaid, glco, glmcu, globj, glsub, gldgj, glsbl, gllt, glctry, glfy, glpn;
    32 --GROUP BY glco, glmcu, globj, glsub, gmr020, gldgj, abac29, glsbl, gllt, glctry, glfy, glpn
    33 VCOMMIT NUMBER(6) := 0;
    34 BEGIN
    35 FOR NOCURSOR IN AUXcursor LOOP
    36 INSERT INTO TMPF0911AUX (glaid, glco, glmcu, globj, glsub --gmr020,
    37 ,gldgj --abac29
    38 , glsbl, gllt, glctry, glfy, glpn, glaa)
    39 VALUES (NOCURSOR.glaid, NOCURSOR.glco, NOCURSOR.glmcu, NOCURSOR.globj, NOCURSOR.glsub --NOCURSOR.gmr020
    40 , NOCURSOR.fecha --NOCURSOR.abac29
    41 , NOCURSOR.glsbl, NOCURSOR.gllt, NOCURSOR.glctry, NOCURSOR.glfy, NOCURSOR.glpn, NOCURSOR.glaa);
    42 VCOMMIT := VCOMMIT + 1;
    43 IF VCOMMIT = 5000 THEN
    44 COMMIT;
    45 VCOMMIT := 0;
    46 END IF;
    47 END LOOP;
    48 COMMIT;
    49* END;
    DECLARE
    *
    ERROR at line 1:
    ORA-01555: snapshot too old: rollback segment number 50 with name "RBS49" too
    small
    ORA-06512: at line 35


    i need help over this query becouse the rollback appear in differents rollback semgnet each execution but this happen when i set a finall date.

    Do you have any idea suggestions?

    Thanks for advance.

    Ismael
    From México.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    remove, eliminate, delete COMMIT on line #44
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Nov 2003
    Posts
    2,932
    Provided Answers: 12
    And upgrade to a supported version of Oracle immediately.

    Besides a bunch of new features, the UNDO handling is also much better in 10.x and 11.x

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •