Why failed with "snapshot too old"
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Why failed with "snapshot too old"

Hybrid View

  1. #1
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233

    Why failed with "snapshot too old"

    Hi guys,

    Attache a package body which failed after 20 minutes running with the "familiar" error message:
    16:32:23 SQL> exec BLD_SIM04_ACC.CREATE_SIM04A_ACC ('MITNO',0);
    BEGIN BLD_SIM04_ACC; END;

    *
    ERROR at line 1:
    ORA-01555: snapshot too old: rollback segment number 17 with name "RBS15" too
    small
    ORA-06512: at "MITNORTH.BLD_SIM04_ACC", line 166

    rbs info:
    ---------
    col SEGMENT_NAME format a20
    select SEGMENT_NAME ,TABLESPACE_NAME,INITIAL_EXTENT/1024/1024 "int",
    NEXT_EXTENT/1024/1024 "next",MIN_EXTENTS "min",MAX_EXTENTS "max"
    from dba_rollback_segs;

    SEGMENT_NAME TABLES int next min max
    -------------------- ------ ---------- ---------- ---------- ----------
    SYSTEM SYSTEM .0546875 .0546875 2 505
    RBS0 RBS 2 2 4 4096
    RBS1 RBS 2 2 4 4096
    RBS2 RBS 2 2 4 4096
    RBS3 RBS 2 2 4 4096
    RBS4 RBS 2 2 4 4096
    RBS5 RBS 2 2 4 4096
    RBS6 RBS 2 2 4 4096
    RBSMITAR RBS 1 1 8 4096
    RBS7 RBS 2 2 4 4096
    RBS8 RBS 2 2 4 4096
    RBS9 RBS 2 2 4 4096
    RBS10 RBS 2 2 4 4096
    RBS11 RBS 2 2 4 4096
    RBS12 RBS 2 2 4 4096
    RBS13 RBS 2 2 4 4096
    RBS14 RBS 2 2 4 4096
    RBS15 RBS 2 2 4 4096

    SQL> select n.NAME,s.EXTENTS,s.RSSIZE,s.OPTSIZE/1024/1024 "opt",
    2 s.HWMSIZE,s.XACTS,s.STATUS
    3 from v$rollname n,v$rollstat s
    4 where n.USN = s.USN ;

    NAME EXTENTS RSSIZE opt HWMSIZE XACTS STATUS
    ---------- ---------- ---------- ---------- ---------- ---------- ----------
    SYSTEM 10 827392 827392 0 ONLINE
    RBS0 4 8380416 8 8380416 0 ONLINE
    RBS1 4 8380416 8 8380416 0 ONLINE
    RBS2 4 8380416 8 8380416 0 ONLINE
    RBS3 4 8380416 8 8380416 0 ONLINE
    RBS4 4 8380416 8 8380416 0 ONLINE
    RBS5 4 8380416 8 8380416 0 ONLINE
    RBS6 4 8380416 8 8380416 0 ONLINE
    RBSMITAR 8 8380416 8 8380416 0 ONLINE
    RBS7 4 8380416 8 8380416 0 ONLINE
    RBS8 4 8380416 8 8380416 0 ONLINE
    RBS9 4 8380416 8 8380416 0 ONLINE
    RBS10 4 8380416 8 8380416 0 ONLINE
    RBS11 4 8380416 8 8380416 0 ONLINE
    RBS12 4 8380416 8 8380416 0 ONLINE
    RBS13 4 8380416 8 8380416 0 ONLINE
    RBS14 4 8380416 8 8380416 0 ONLINE
    RBS15 4 8380416 8 8380416 0 ONLINE

    18 rows selected.

    I've added,enlarged rbs,added/removed "commit" from the code,but nothing helped!

    It always failed on the same line: 166
    line 166 start with:
    FOR REC_CHNG IN CUR_CHNG LOOP
    P_MIS_HORAAT_AVODA := REC_CHNG.MIS_HORAAT_AVODA;
    P_MIS_GIRSA := REC_CHNG.MIS_GIRSA;
    P_SUG_HORAA := REC_CHNG.SUG_HORAA;
    BEGIN
    DELETE FROM TMP_SIMULAZIA_04
    WHERE SUG_HORAA = P_SUG_HORAA
    AND MIS_GIRSA = P_MIS_GIRSA
    AND MIS_HORAAT_AVODA = P_MIS_HORAAT_AVODA;
    commit;
    EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
    END;
    SW_YESH_NKUDOT_LBIZUA := 0;
    FOR REC_NK IN CUR_NK LOOP
    L_MIS_SHOVAR := NULL;
    L_MACHSAN := NULL;
    L_MIS_HORAAT_BIZUA := REC_NK.MISPAR_BIZUA_HORAA;
    L_MIS_NKUDA := REC_NK.MISPAR;
    L_PEULA := REC_NK.PEULA;

    If anyone see something wrong in the code ,i'll be glad to know.

    Thanks in advance,
    Nir
    Attached Files Attached Files

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    Re: Why failed with "snapshot too old"

    Originally posted by nir_s
    It always failed on the same line: 166
    line 166 start with:
    FOR REC_CHNG IN CUR_CHNG LOOP
    P_MIS_HORAAT_AVODA := REC_CHNG.MIS_HORAAT_AVODA;
    P_MIS_GIRSA := REC_CHNG.MIS_GIRSA;
    P_SUG_HORAA := REC_CHNG.SUG_HORAA;
    BEGIN
    DELETE FROM TMP_SIMULAZIA_04
    WHERE SUG_HORAA = P_SUG_HORAA
    AND MIS_GIRSA = P_MIS_GIRSA
    AND MIS_HORAAT_AVODA = P_MIS_HORAAT_AVODA;
    commit;
    EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
    END;
    If anyone see something wrong in the code ,i'll be glad to know.
    Clasical example of fetch accross commitĘ! Remove that COMMIT statement inside your cursor-for loop, otherwise you'll never get rid of ORA-1555. (I noticed you have *many* commits inside open cursors in your code - very very very bad practice, you should never do a commit onside an open cursor unless you are using authonomous transactions. Remove all those comits from your code.) I also suggest you to read some books or look around on the net to understand why and when you are getting ORA-1555 error. You can also use the "Search" facility of this forums, as "sanpshot too old" errors have been axplained in detals many times here.

    And BTW, your exception WHEN_NO_DATA_FOUND in the above block is totaly useles, as this exception can never happen with DELETE statement - you should use this exception only with SELECTs!
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Jul 2003
    Posts
    59
    Also, look into writing this delete in a single SQL statement.
    Tom Best

  4. #4
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    Hi,

    I've removed all the commits,and now it fails after 9 minutes!
    09:14:52 SQL> exec BLD_SIM04_ACC.CREATE_SIM04A_ACC ('MITNO',0);
    BEGIN BLD_SIM04_ACC; END;
    *
    ERROR at line 1:
    ORA-01555: snapshot too old: rollback segment number 11 with name "RBS9" too small
    ORA-06512: at "MITNORTH.BLD_SIM04_ACC", line 164
    ORA-06512: at line 1


    Elapsed: 00:09:09.38

    Any suggestions?

    Thanks.
    Attached Files Attached Files

  5. #5
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    are you sure no other sessions are performing DML the table tmp_simulazia_04?

    And this part of code isnt taking long time to run?

    Code:
        SELECT t04.mis_horaat_avoda,t04.mis_girsa,t04.sug_horaa      
        FROM   tmp_simulazia_04 t04                                  
        WHERE  exists (select '1' from pritim p1                     
                where p1.MIS_KATALOGY = t04.MIS_KATALOGY_MITAR)      
        AND  exists (select '1' from pritim p                        
                where p.MIS_KATALOGY = t04.MIS_KATALOGY_MITAR        
                 and  p.mecir_atidi != t04.mecir_MITAR_atidi)        
         and  not exists(select '1' from tmp_simulazia_for_changes sf
                  where sf.mis_horaat_avoda = t04.mis_horaat_avoda   
                    and sf.mis_girsa        = t04.mis_girsa          
                    and sf.sug_horaa        = t04.sug_horaa);


    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  6. #6
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    Hi abhaysk,

    Yes i'm sure - there are no ather sessions parallely.
    I'm working now in test environment and i'm "lonely" there.

    The select statement you've asked about fetches 264 rows after 45 secondes. Is it too slow?

  7. #7
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    Hi guys,

    I want to update you that i found in the code a call for anothe package which contain a procedure with a commit.
    In addition,i've put one commit in the end of the package ,out of the loops and assign the transaction to a big rbs.
    Now it is running (1 hour past). It suppose to be run in 2 hours.

    SQL> select sum(bytes/1024/1024),extents
    2 from dba_segments
    3 where segment_name='RBSMITAR'
    4 group by extents;

    SUM(BYTES/1024/1024) EXTENTS
    -------------------- ----------
    440 88


    I hope it will survive...

Posting Permissions

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


Click Here to Expand Forum to Full Width