DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Why failed with "snapshot too old"

Threaded 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

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