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;
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.