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