1 Attachment(s)
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
Re: Why failed with "snapshot too old"
Quote:
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!